Update 2009-12-22 – Added information and link on getting the ID from .NET DbCommand object.
The Problem is that we have a table that uses the NewSequentialID() function on the Default constraint. We want to be able to run a stored procedure to insert a new row and return the newly inserted GUID generated by NewSequentialID() much like we would when we have an IDENTITY column and use @@IDENTITY or SCOPE_IDENTITY().
I recently read an article that suggests the creation of a trigger that get’s the ID that NewSequentialID() generated from the Inserted table (I’ve not tested this) (See idea 2 here)
There seems to be a better way gleaned from this article… I never knew you could do this!
First Lets Create our Table (Run this on a test database!!!)
CREATE TABLE MyEntity( EntityID uniqueidentifier NOT NULL PRIMARY KEY DEFAULT NewSequentialID(), EntityName nvarchar(100) NOT NULL ); GO
And now a sproc to do the insert. Notice the Output clause!!!!
CREATE PROCEDURE MyEntity_Insert AS BEGIN SET NOCOUNT ON; INSERT INTO MyEntity (EntityName) OUTPUT inserted.EntityID VALUES('William'); END GO
Now lets call it and get the returned value.. While we are at it we will SELECT all the rows ( all one of them 🙂 ) and compare the EntityID values.
DECLARE @return_value int EXEC @return_value = [dbo].[MyEntity_Insert] SELECT 'Return Value' = @return_value SELECT * FROM MyEntity
Just to clean up!
DROP PROCEDURE MyEntity_Insert; DROP TABLE MyEntity;
.Net – Getting the newly Generated Guid
Bloke at work directed me to this article which shows how to get this value from .NET (at the time of the original writing of this post, I was only interested in getting the Sql working… seeing if it was possible from the database side to do what I wanted… We were planning at the time.)
To get the OUTPUT value in the above case you should just have to go.
var id = (Guid)command.ExecuteScalar();