Before SQL Server 2005 I used to return generated identity value using either SELECT [column list] or RETURN scope_identity() depending on the type or amount of returned values.
In this article I’ll show you how to use the OUTPUT with ADO.NET for catching the returned value(s).
T-SQL and OUTPUT
SQL Server 2005 brought the new OUTPUT to the DML-statements INSERT, UPDATE and DELETE. Here is a good post about the subject from the T-SQL perspective.
Typical INSERT-operation using OUTPUT could look like this:
([ProductName], CreatedAt, CreatedBy)
OUTPUT Inserted.ProductID
VALUES
(@ProductName, GetDate(), @CreatedBy)
If you have ever created triggers, the Inserted may seem familiar. You can think it as a virtual table that has the inserted row.
Similarly DELETE-statement returns a virtual table called “Deleted” while UPDATE-statement returns both. Note: If your table has after triggers, they may override the OUTPUT so test these cases carefully.
ADO.NET -code when returning a single value
Good news for coders is that if you have used SELECT after the INSERT-statement, there are necessarily no need to do any changes to your code. Just change the T-SQL to use OUTPUT and you are done.
Here is a helper method:
public T ExecuteNonQueryReturnIdentity<T>(SqlCommand sqlCommand)
{
using (var conn = new SqlConnection(ConnectionString))
{
sqlCommand.Connection = conn;
conn.Open();
T result = (T)sqlCommand.ExecuteScalar();
conn.Close();
sqlCommand.Dispose();
return result;
}
}
In the example above I use generics to encapsulate the conversion to given type. I’d call the above query like this:
//sqlCommand contains the T-SQL and parameters that the query
//expects
int productID = ExecuteNonQueryReturnIdentity<int>(sqlCommand);
ADO.NET -code when returning multiple values
Sometimes you may want to return multiple values using the OUTPUT.
Then you may execute the query as normal SqlDataReader, when many rows are expected (typically UPDATEs and DELETEs):
SqlDataReader reader;
reader = sqlCommand.ExecuteReader(CommandBehavior.SingleResult);
For single line returns (typically INSERTs) I recommend you to use ExecuteReader with the SingleRow -command behavior:
SqlDataReader reader;
reader = sqlCommand.ExecuteReader(CommandBehavior.SingleRow);
Does it work with uniqueidentifiers/GUID’s?
One advantage of this method is that the T-SQL and ADO.NET-code is the same when using any type for the returned values.
Here is an example using the helper method above:
Guid newGuid = ExecuteNonQueryReturnIdentity<Guid>(sqlCommand);
While I’m not a fan of using uniqueidentifiers as primary keys due to performance reasons, I sometimes use them on another columns and normal integer for the primary column. With OUTPUT you can easily return both values:
([ProductName], GUID, CreatedAt, CreatedBy)
OUTPUT Inserted.ProductID, Inserted.GUID
VALUES
(@ProductName, newid(), GetDate(), @CreatedBy)
Using OUTPUT with automated tests
When you are doing unit and integration tests, you could use OUTPUT to verify that correct results happen at the database with less round-trips.
How about performance?
Here is a quote from the John Paul Cook’s post:
“Sometimes, people will ask if an INSERT with an OUTPUT clause is really shorthand for SQL Server 2005 to internally execute a SELECT after an INSERT. The definitive way to answer questions about what takes place internally is to run the SQL Profiler. The INSERT with the OUTPUT appears as a single statement in the Profiler, not as an INSERT followed by a SELECT. This is important from a performance perspective because a single statement means a single trip to the database.”
When using OUTPUT with the DML-statement, SQL Server sees it as one operation whereas INSERT and separate SELECT would be seen as two separate operations. The less operations there are to the database, the better so there is a marginal benefit when using OUTPUT.
Conclusion
OUTPUT is a simple and effective way to return values after modifying tables. ADO.NET code is simple and doesn’t require learning any new tricks.
