Returning GUID values to ADO.NET clients

In my last post I showed how the OUTPUT clause could be used to return a GUID generated as the default of a column using the NEWSEQUENTIALID() function. The code for creating the database, table and procedure can be found here. Because this is a standard output parameter being returned one way to get this value is to use the following code:

conn = new SqlConnection("Data Source=(local);Initial Catalog=GUIDTEST;Integrated Security=SSPI");
conn.Open();

cmdProc1 = new SqlCommand("usp_AddOrder", conn);
cmdProc1.CommandType = CommandType.StoredProcedure;

SqlParameter param_OrderGUID = new SqlParameter("@OrderGUID", System.Data.SqlDbType.UniqueIdentifier);
param_OrderGUID.Direction = ParameterDirection.Output;
cmdProc1.Parameters.Add(param_OrderGUID);
cmdProc1.ExecuteNonQuery();

Console.WriteLine(String.Format("usp_AddOrder Returned GUID: {0}", param_OrderGUID.Value.ToString()));

In general it is advised to return single values as a parameters because it is more efficient, but remembering Tony Rogerson’s blog post, there may be occasions to insert multiple rows in one stored procedure. In this instance you may want to return all of the GUIDs as a resultset by changing the line in the procedure:

SET @OrderGUID = (SELECT TOP 1 [OrderGUID] FROM @OrderGUIDs ) ;

to

SELECT [OrderGUID] FROM @OrderGUIDs;

There is also a form of the OUTPUT CLAUSE that will allow you to do this without the need for using the interim table variable. This is to simply use OUTPUT without INTO clause. usp_AddOrder2 will add three new rows to the dbo.Orders table and return all three GUIDs by only using the OUTPUT clause. In ADO.NET this can be treated like a normal result set. I’ve written a simple test program which will run each procedure and return the GUIDs.