CODEWITHSUNDEEP
sql-server-2008stored-proceduresprimary-keyguid
ImGreg
ImGreg

Reputation: 2983

Return GUID from Stored Procedure Insert

I have some stored procedures where I do inserts into my SQL Server db. I would like to return this GUID for use for future inserts, or w/e I find use for it as.

I know I could generate the GUID in the procedure and return it someway from that, but I'd like to avoid generating it there as it has a default value for the primary key (guid) row. I recall a function like SCOPE_IDENTITY for returning the identity column you just used, anything similar to this as a GUID?

Help is appreciated! Thanks :)

Upvotes: 3

Views: 7529

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can use the output clause from the insert statement.

Return as a result set from the SP like this. ID is a uniqueidentifier with default newid().

insert into YourTable(Col1)
output inserted.ID 
values (1)

Or capture to a table variable and do what ever you need after that.

declare @T table(ID uniqueidentifier)

insert into YourTable(Col1)
output inserted.ID into @T 
values (1)

select *
from @T

Upvotes: 8

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

No, there is no SCOPE_GUID() or similar. Why do you care if you use the default value defined on the column or if you declare the value yourself and insert it manually? If you were using NEWSEQUENTIALID() I would understand, but for NEWID() I would just say create it first and insert it manually. This will allow you to return the generated value without querying the table again (you'd need to identify the new row in some other way). SQL Server won't be offended if you don't use the default value; that's there for a fallback in the event the value isn't provided explicitly.

Upvotes: 2

Related Questions