In a highly concurrent application it could (theoretically) happen that data you&#39;ve read in the first select is modified before the other selects are executed.

If that is a situation that could occur in your application you should use a transaction to wrap your selects. Make sure you pick the correct [isolation level][1] though, not all transaction types guarantee consistent reads.

**Update** : 
You may also find [this article][2] on concurrent update/insert solutions (aka upsert) interesting. It puts several common methods of upsert to the test to see what method actually *guarantees* data is not modified between a select and the next statement. The results are, well, shocking I&#39;d say.


  [1]: http://msdn.microsoft.com/en-us/library/ms173763.aspx
  [2]: http://michaeljswart.com/2011/09/mythbusting-concurrent-updateinsert-solutions/

**Transactions** are usually used when you have `CREATE`, `UPDATE` or `DELETE` statements and you want to have the **atomic behavior**, that is, *Either commit everything or commit nothing*.  

However, you could use a transaction for `READ` select statements to:  
**Make sure nobody else could update the table of interest while the bunch of your select query is executing.** 

Have a look at this [msdn post][1].


  [1]: http://msdn.microsoft.com/en-us/library/ms188929.aspx

How would I specify a JPA query like:

    Query q = 
      em.createQuery(
        &quot;SELECT x FROM org.SomeTable x WHERE x.someString LIKE &#39;%:someSymbol%&#39;&quot;
      );

followed by:

    q.setParameter(&quot;someSymbol&quot;, &quot;someSubstring&quot;);

and not triggering a

    org.hibernate.QueryParameterException: could not locate named parameter [id]

Much appreciated!

How to specify a JPA named parameter surrounded by wildcards?

Is there any way to access caller-scoped variables from an anonymous inner class in Java?

Here&#39;s the sample code to understand what I need:

    public Long getNumber(final String type, final String refNumber, final Long year) throws ServiceException {
        Long result = null;
        try {
            Session session = PersistenceHelper.getSession();
            session.doWork(new Work() {
                    public void execute(Connection conn) throws SQLException {
                        CallableStatement st = conn.prepareCall(&quot;{ CALL PACKAGE.procedure(?, ?, ?, ?) }&quot;);
                        st.setString(1, type);
                        st.setString(2, refNumber);
                        st.setLong(3, year);
                        st.registerOutParameter(4, OracleTypes.NUMBER);
                        st.execute();
                        result = st.getLong(4) ;
                    }
                });
        } catch (Exception e) {
            log.error(e);
        }
        return result;
    }

The code is in a DAO service class. Obviously it doesn&#39;t compile, because it asks that `result` be final, if it is -- it doesn&#39;t compile because I try to modify a final var. I&#39;m bound to JDK5. Other than dropping the `doWork()` altogether, is there a way to set the result value from within `doWork()`?

Setting outer variable from anonymous inner class

I don&#39;t use Stored procedures very often and was wondering if it made sense to wrap my select queries in a transaction.

My procedure has three simple select queries, two of which use the returned value of the first.

Use transactions for select statements?

<p>I don't use Stored procedures very often and was wondering if it made sense to wrap my select queries in a transaction.</p>
<p>My procedure has three simple select queries, two of which use the returned value of the first.</p>


I am getting following error in my SQL server 2008 R2 database:

&gt; Cannot use a `CONTAINS` or `FREETEXT` predicate on table or indexed view &#39;tblArmy&#39; because it is not full-text indexed.



Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed

I have a table with a very large amount of rows. Duplicates are not allowed but due to a problem with how the rows were created I know there are some duplicates in this table.
I need to eliminate the extra rows from the perspective of the key columns. Some other columns may have *slightly* different data but I do not care about that. I still need to keep one of these rows however. SELECT DISTINCT won&#39;t work because it operates on all columns and I need to suppress duplicates based on the key columns.

How can I delete the extra rows but still keep one efficiently?

T-SQL: Deleting all duplicate rows but keeping one

Is it possible to get the name of the current Stored Procedure in MS SQL Server? 

Maybe there is a system variable or function like `GETDATE()`?

Current executing procedure name

I have a bunch of product orders and I&#39;m trying to group by the date and sum the quantity for that date. How can I group by the month/day/year without taking the time part into consideration?

`3/8/2010 7:42:00` should be grouped with `3/8/2010 4:15:00`



How can I group by date time column without taking time into consideration

I&#39;m trying to get the total number of days between two days:

    1/1/2011
    3/1/2011

    RETURN
    62

Is it possible to do in SQL Server?

SQL SERVER: Get total days between two dates

Here is my T-SQL query

	SELECT 
		ProductID,
		VendorID,
		ProductName= MAX(ProductName),
		VendorName = MAX(VendorName),
		IsActive = MAX(IsActive) # This brings error 
	FROM ProductVendorAssoc 
	GROUP BY  
		ProductID,
		VendorID
			
I want to apply `GROUP BY` only for the `ProductID and VendorID` fields, but need to populate the `ProductID, VendorID, ProductName, VendorName, IsActive` fields.

Here I used the agreggate function `MAX(ProductName)` to avoid `ProductName` in the group by list. 

But the same trick is not working for `BIT` columns as operand data type bit is invalid for max operator.

How can i include `BIT` type column in `SELEC`T part with out including it on the `GROUP BY`?

**Update.**

What should I need to do if i need to include an `INT` column like `UserID` in `SELECT` in the same way

How to include BIT type column in SELECT part with out including it on the GROUP BY in T-SQL?

I created a stored procedure in phpMyAdmin

    CREATE PROCEDURE Sample()
    SELECT * FROM feedback

Where could I view this this procedure? If it&#39;s not possible in phpMyAdmin, what is/are some good program(s) that have the feature to write, store, and view stored procedures, tables, etc?

How do I view my stored procedures in phpMyAdmin?

I am trying to call a stored procedure from my C# windows application. The stored procedure is running on a local instance of SQL Server 2008. I am able to call the stored procedure but I am not able to retrieve the value back from the stored procedure. This stored procedure is supposed to return the next number in the sequence. I have done research online and all the sites I&#39;ve seen have pointed to this solution working.

Stored procedure code:

    ALTER procedure [dbo].[usp_GetNewSeqVal]
          @SeqName nvarchar(255)
    as
    begin
          declare @NewSeqVal int
          set NOCOUNT ON
          update AllSequences
          set @NewSeqVal = CurrVal = CurrVal+Incr
          where SeqName = @SeqName
         
          if @@rowcount = 0 begin
    print &#39;Sequence does not exist&#39;
                return
          end
     
          return @NewSeqVal
    end

Code calling the stored procedure:

    SqlConnection conn = new SqlConnection(getConnectionString());
    conn.Open();
    
    SqlCommand cmd = new SqlCommand(parameterStatement.getQuery(), conn);
    cmd.CommandType = CommandType.StoredProcedure;
    
    SqlParameter param = new SqlParameter();
    
    param = cmd.Parameters.Add(&quot;@SeqName&quot;, SqlDbType.NVarChar);
    param.Direction = ParameterDirection.Input;
    param.Value = &quot;SeqName&quot;;
    
    SqlDataReader reader = cmd.ExecuteReader();

I have also tried using a `DataSet` to retrieve the return value with the same result. What am I missing to get 
the return value from my stored procedure? If more information is needed, please let me know.

Calling stored procedure with return value

I have very simple question but i did&#39;t get any simple code to exit from SP using Mysql.
Can anyone share with me how to do that?

    CREATE PROCEDURE SP_Reporting(IN tablename VARCHAR(20))
    BEGIN
         IF tablename IS NULL THEN
              #Exit this stored procedure here
         END IF;
    
         #proceed the code
    END;

Mysql - How to quit/exit from stored procedure

Is there any way to use Dapper.NET with stored procs that return multiple result sets?

In my case, the first result set is a single row with a single column; if it&#39;s `0` then the call was successful, and the second result set will contain that actual rows/columns of data. (and if it was non-zero, an error occured and no second result set will be provided)

Any chance to handle this with Dapper.NET? So far, I&#39;m only ever getting back that single `0` - but nothing more.

**Update:** OK, it works fine - as long as the result set no. 2 is a single entity:

    Dapper.SqlMapper.GridReader reader = 
        _conn.QueryMultiple(&quot;sprocname&quot;, dynParams, 
        commandType: CommandType.StoredProcedure);

    int status = reader.Read&lt;int&gt;().FirstOrDefault();
    MyEntityType resultObj = reader.Read&lt;MyEntityType&gt;().FirstOrDefault();

Now, I have **yet another** requirement.

Dapper&#39;s multi-mapping (splitting up a single row returned from SQL Server into two separate entities) for that second result set doesn&#39;t seem to be supported as of yet (at least there doesn&#39;t seem to be an overload of `.Read&lt;T&gt;` that can handle multi-mapping).

How can I get split that row into two entities?

Content Type	Original Author	Original Content on Stackoverflow
Question	chobo	View Question on Stackoverflow
Solution 1 - Sql Server	Marnix van Valen	View Answer on Stackoverflow
Solution 2 - Sql Server	FIre Panda	View Answer on Stackoverflow

Use transactions for select statements?

Sql Server Problem Overview

Sql Server Solutions

Solution 1 - Sql Server

Solution 2 - Sql Server

Setting outer variable from anonymous inner class

How to specify a JPA named parameter surrounded by wildcards?

Attributions