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
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:
More information about the OUTPUT clause can be found in SQL Server Books Online at http://msdn.microsoft.com/en-us/library/ms177564.aspx.
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!”