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!”