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

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New


RAC is a powerful data manipulation utility that simplifies tasks like crosstabs, pivoting data, string manipulations, hierarcharical processing and more! Check it out NOW!


 
NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line
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:
The guru's guide to Transact SQL Advanced Transact-SQL for SQL Server 2000 SQL Server 2000 Programming by example

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

Idea 2

Assign NEWID() as a default to your GUID column and use an INSERT trigger to return the GUID value back to the client as a resultset. Here's a sample schema, trigger and VB/ADO code:


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

Idea 3

Instead of worrying about returning the GUID values back to the client, why not generate the GUID values from within the client? :-) For more information and sample code, read this article from Microsoft Knowledgebase:

HOWTO: Use CoCreateGUID API to Generate a GUID with VB (Q176790)