Share via

Unexpected Consequences of Multiple Result Sets

  • Article

Author:             Chuck Heinzelman

Reviewers:      Kevin Cox, Kun Cheng, Michael Thomassy

In a recent customer engagement, I was presented with a problem that I have seen in the past and am surprised that I don’t see more often.

Take the following table as an example:

CREATE TABLE dbo.ResultSetTest

(

   ID integer IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

   TestData varchar(255) NOT NULL

);

 

This is a simple table with an Identity column – I’m sure most of us have a table like this (with more columns, of course) in the systems that we work with.

The customer had code to insert a new row into the table and return to the calling application the value assigned to the ID column.  The code was similar to the following:

INSERT INTO dbo.ResultSetTest

(TestData)

VALUES
(‘Test’);

SELECT @@Identity AS ID;

 

Given this T-SQL batch, how many result sets would you expect to receive?  If you answered 1 I would not be surprised – as this is what my customer was expecting.  In fact – depending on the driver and execution method used (we were using the Microsoft JDBC Driver for SQL Server) – this T-SQL batch can generate 2 result sets (which is what my customer was seeing).  The first result set (from the INSERT statement) was empty, and the second result set (from the SELECT statement) contained the value of the ID column.

So, how do you handle a situation like this?  I can think of several ways:

  1. Change Your Code – You could change the code to move to the second result set in the collection of result sets that are returned.  This would allow you to get the value of the ID column without changing your T-SQL batch.  You can find an example of how to accomplish this with the Microsoft JDBC driver on the Microsoft JDBC Driver Team Blog (https://blogs.msdn.com/b/jdbcteam/archive/2008/08/01/use-execute-and-getmoreresults-methods-for-those-pesky-complex-sql-queries.aspx).
  2. Use a Stored Procedure – You could use a stored procedure to output the value of the ID column as either the return value (provided it is an integer type) or through an output parameter.  Using stored procedures is a long-standing best practice and it allows you to have more control over the results being returned.  Also, consider including SET NOCOUNT ON in your stored procedure definition to exclude extraneous rows affected messages.
  3. Change Your T-SQL Batch – By rewriting the batch into a single statement, you can work around the multiple result sets without any other code changes.  The new statement looked something like this:

INSERT INTO dbo.ResultSetTest

(TestData)

OUTPUT Inserted.ID

VALUES
(‘Test’);

 More information about the OUTPUT clause can be found in SQL Server Books Online at https://msdn.microsoft.com/en-us/library/ms177564.aspx.

 

Conclusion

 When writing T-SQL statements and batches, you need to be aware of the clients that your data consumers will be using to retrieve the data.  Different drivers can act differently, and you need to be prepared when you get calls saying “The statement you wrote is not doing what it should be doing!”

Comments

  • Anonymous
    June 02, 2011
    What was the connection string properties of the connection that generated this behaviour?

  • Anonymous
    June 05, 2011
    Of course, you shouldn't use "@@IDENTITY" at all; use "SCOPE_IDENTITY()" or the OUTPUT clause instead.

  • Anonymous
    June 07, 2011
    Chuck, I didnt know about that behavior, that was worth a post. I like the output option as well. I also prefer scope_identity() over @@Identity, just to make sure that some future trigger doesn't cause me to get unexpected values back.

  • Anonymous
    June 07, 2011
    The comment has been removed

  • Anonymous
    June 07, 2011
    Andy- Thanks for the comment. I've seen the behavior in the past, but until now I've never had a real reason to track down what was happening or why.  Depending on the provider, SET NOCOUNT ON might solve the problem (no results from the first statement), but that should be tested to make sure that it does not cause other problems. Chuck

  • Anonymous
    June 08, 2011
    Another way to handle the situation is to suppress the first record set from the insert by adding SET NOCOUNT ON.  Then, before SELECT for the new identifier, SET NOCOUNT OFF.  The second setting is not needed because the SELECT will return the records to the caller anyway, even without the NOCOUNT setting turned "OFF".

  • Anonymous
    June 15, 2011
    StackOverflow has (my) question abut SET NOCOUNT ON: stackoverflow.com/.../27535 The last update was to show a similar issue with JPA and stored procedures: stackoverflow.com/.../27535