Returning the NewSequentialID() after Insert using the Output Clause

Update 2009-12-22 – Added information and link on getting the ID from .NET DbCommand object.

The Problem

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().

Solution(s)

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!

See Also: Sql Server Books Online details on the Output Clause

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

Update 2009-12-22

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();
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: