How to retrieve the newly inserted GUID value?
Last updated: May 30th '02 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

See also:   Database coding conventions SQL Server interview questions Evaluation of federated database servers SQL Server FAQ on programming, administration, replication and tools SQL Server security best practices SQL Server administration best practices

Related books:

How to retrieve the newly inserted GUID value? Is there something like @@GUID, similar to @@IDENTITY ?


This is yet another FAQ from Microsoft public SQL Server newsgroups. Lot of SQL Server developers are used to using @@IDENTITY to retrieve the value of the IDENTITY column immediately after inserting a row. We are missing that functionality when it comes to inserting rows with GUIDs as keys. Unfortunately, there's no equivalent to @@IDENTITY, when it comes to GUIDs.

Here are three ideas to help you retrieve the GUID value immediately after inserting a row.

Idea 1

As you know, T-SQL offers the NEWID() function to generate GUID values. To be able to retrieve GUID value after inserting a row, do not assign NEWID() as a default to your key column. Instead, use a stored procedure to insert new rows. Inside this stored procedure, generate a GUID value before hand, save it in a variable for later use, and then insert the new row with the generated GUID. Here is an example table schema and a sample stored procedure along with VB code that uses ADO to call this stored procedure:

--Table structure
CREATE TABLE dbo.GUIDTable
(
	GUIDCol		uniqueidentifier NOT NULL PRIMARY KEY,
	OtherColumn	varchar(25) NOT NULL
	
)
GO



--Stored procedure for inserting rows
CREATE PROCEDURE dbo.InsertGUIDs
(
	@OtherColumn	varchar(25)
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @GUIDCol uniqueidentifier

	SET @GUIDCol = NEWID() --Creating the new GUID value before hand

	INSERT INTO dbo.GUIDTable (GUIDCol, OtherColumn) VALUES (@GUIDCol, @OtherColumn)

	IF @@ERROR = 0 AND @@ROWCOUNT = 1
	BEGIN
		/* This GUID will be returned to the client as a recordset */
		SELECT @GUIDCol AS '@@GUID'
		RETURN 0
	END
	ELSE
	BEGIN
		RAISERROR('Something went wrong :-(', 16, 1)
		RETURN -1
	END
END
GO



--Sample calls to the stored procedure
EXEC dbo.InsertGUIDs 'SQL Cluster'
EXEC dbo.InsertGUIDs 'Replication'
EXEC dbo.InsertGUIDs 'Network Load Balancing'
EXEC dbo.InsertGUIDs 'NLB & Cicso'
EXEC dbo.InsertGUIDs 'Oracle, Sybase, DB2'
GO



'Minimal VB code that uses ADO to insert new rows and retrieve the GUID values back

Dim MyServer As New ADODB.Connection, RS As New ADODB.Recordset

MyServer.Open "Server=ServerName;UID=AppUser;PWD=RandomPassword;Provider=SQLOLEDB;Database=AppDB"

Set RS = MyServer.Execute("EXEC dbo.InsertGUIDs 'SQL Cluster'")

If Err.Number = 0 And RS.State = adStateOpen Then
    MsgBox "Your CustomerID is " + RS("@@GUID")
End If

--Table structure
CREATE TABLE dbo.GUIDTable2
(
	GUIDCol		uniqueidentifier NOT NULL PRIMARY KEY DEFAULT (NEWID()),
	OtherColumn	varchar(25) NOT NULL
	
)
GO



--Insert trigger that returns a recordset containing the generated GUIDs to client
CREATE TRIGGER dbo.InsertTriggerOnGUIDTable2
ON dbo.GUIDTable2
FOR INSERT
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT GUIDCol AS '@@GUID'
	FROM inserted
END
GO



--Sample INSERT statements:
INSERT INTO dbo.GUIDTable2 (OtherColumn) VALUES ('Performance tuning')
INSERT INTO dbo.GUIDTable2 (OtherColumn) VALUES ('NAS and EMC SAN')
INSERT INTO dbo.GUIDTable2 (OtherColumn) VALUES ('Alerts and Thresholds')
INSERT INTO dbo.GUIDTable2 (OtherColumn) VALUES ('BCP, DTS! which is best?')
INSERT INTO dbo.GUIDTable2 (OtherColumn) VALUES ('SQL Server on IIS')
INSERT INTO dbo.GUIDTable2 (OtherColumn) VALUES ('Web Server Performance')

INSERT INTO dbo.GUIDTable2 (OtherColumn) 
SELECT 'xp_sendmail problems!' 
UNION ALL 
SELECT 'SQL Mail or SMTP Mail?' 
UNION ALL 
SELECT 'SQL Reserved Words'
GO




'Minimal VB code that uses ADO to insert new rows and retrieve the GUID values back
Dim MyServer As New ADODB.Connection, RS As New ADODB.Recordset

MyServer.Open "Server=ServerName;UID=AppUser;PWD=RandomPassword;Provider=SQLOLEDB;Database=AppDB"

Set RS = MyServer.Execute("INSERT INTO dbo.GUIDTable2 (OtherColumn) SELECT 'xp_sendmail problems!'")

If Err.Number = 0 And RS.State = adStateOpen Then
    Do While Not RS.EOF
        MsgBox "Here are the new IDs " + RS("@@GUID")
        RS.MoveNext
    Loop
End If