Returning GUID values to ADO.NET clients

Published 09 June 09 08:44 AM | MartinBell

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.

Comments

# Dew Drop - June 9, 2009 | Alvin Ashcraft's Morning Dew said on June 9, 2009 02:03 PM:

Pingback from  Dew Drop - June 9, 2009 | Alvin Ashcraft's Morning Dew

# Sanjeev Agarwal said on June 10, 2009 01:51 PM:

Daily tech links for .net and related technologies - June 10, 2009 Web Development Introduction to StructureMap

# Martin Bell UK SQL Server MVP said on June 14, 2009 05:39 PM:

In my previous posts I have looked at using the OUTPUT clause to return GUID values generated by the database and how you can return these to an ADO.Net application but this doesn't really give an indication of the power and usefulness that clause

This Blog

SQL Blogs

Syndication