Unexpected Consequences of Multiple Result Sets

Unexpected Consequences of Multiple Result Sets

Rate This
  • Comments 7

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 (http://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 http://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!”

Leave a Comment
  • Please add 3 and 4 and type the answer here:
  • Post
  • What was the connection string properties of the connection that generated this behaviour?

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

  • 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.

  • Richard-

    Thank you for the comment.

    I would not say that you shouldn't use @@IDENTITY at all...but it is important to know your environment and understand that it could return a different result than you may expect.

    Chuck

  • 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

  • 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".

  • 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

Page 1 of 1 (7 items)