Return GUID from Stored Procedure Insert

Question

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 :)

Mikael Eriksson · Accepted Answer · 2011-07-11 18:41:32Z

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

Aaron Bertrand · Accepted Answer · 2011-07-11 18:27:06Z

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.

i'd rather avoid this if possible -- mainly because i don't want to rewrite like 50 stored procedures to accommodate something that the db takes care of already, but i guess you gotta do what you gotta do sometimes. would you just: DECLARE @GUID uniqueidentifier, SET @GUID = NEWID(), and at the end, return @GUID?
Yep. If you need to return the GUID to the application, you're going to have to change 50 procedures anyway, right? This is what refactoring is all about.
I feel dumb that I didn't realize I could just return the NEWID() lol

Collectives™ on Stack Overflow

Return GUID from Stored Procedure Insert

2 Answers 2

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Related