Something I looked-up just to confirm what I already knew– there isn’t a way to automagically get the value of a GUID after inserting into SQL Server by using something similar to SELECT @@identity as you would with normal identity column. Instead you can do this:
Now, unlike an IDENTITY column, a uniqueidentifier column doesn’t automagically get an assigned value when a row is inserted into a table. You either need to place a default on the uniqueidentifier column (DEFAULT NEWID()), or do something like the following:
DECLARE @GUID uniqueidentifier
SET @GUID = NEWID()
INSERT Item VALUES (@GUID,’Yak Hoof’)
As a sidenote, I’ve not worked in SQL Server nearly as much the past couple of years as I have previously because our primary database platform is something different at work, so there are a few things I’m rusty with for sure.
4 Responses
Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.
hang a trigger off that table to return that UID column, the NewID() function might be better used as the default vaue in that table’s definition. less “codey” anyways.
Paul,
As I need the GUID for a couple of more queries in this particular Stored Proc, I don’t mind creating the GUID first and using them on the subsequent inserts as well rather than doing a Stored Proc AND a Trigger. Good suggestion though.
Hi, Brandon.
I develop CF apps and our shop has wrestled with the return-the-new-id thing in a few different contexts. Lately we’ve switched from GUID / UUID primary keys in our SQL Server db’s to non-identity integers, but that’s another story…
Below is a sample stored procedure of the sort we have used to insert a record and retrieve its primary key in one operation.
CREATE PROCEDURE insert_and_get_newGroup
@GroupTypeID uniqueidentifier,
@GroupName nvarchar(50),
@GroupID uniqueidentifier output
AS
SET @GroupID = newid()
BEGIN TRANSACTION NewGroup
insert into Groups
(GroupID, FKGroupTypeID, GroupName)
values
(@GroupID, @GroupTypeID, @GroupName)
COMMIT TRANSACTION NewGroup
GO
The other thing we have most often done when we need a GUID for multiple queries in a transaction (e.g. insert a record and then use its PK as foreign key to update a bunch of related tables) is generate it in CF rather than the database. CF’s UUID type is accepted by SQL Server as a GUID if you insert an extra hyphen and pass it in as a string.
We are rewriteing our database to use NEWID() for a security question. Using GUID is impossible to any user guess any information. This is important for a large multi-user system that use web as platform.