Blog

Returning a uniqueidentifier from MS SQL Server in Coldfusion

September 22, 2007

I ran into a problem trying to return the newly created identity from a table. The identity column was a uniqueidentifier type.

Table structure:

I needed to retrieve the new UniqueID value after an INSERT statement.

The default value for UniqueID is set to newid().

Here is my original code:

<cfquery name="insert" datasource="#DSN#">
INSERT INTO foo (textfield) VALUES ('test1')
SELECT SCOPE_IDENTITY() AS NewUniqueID
</cfquery>

<cfset newid = insert.NewUniqueID>

The newid variable is always an empty string.

I also tried:

<cfquery name="insert" datasource="#DSN#">
INSERT INTO foo (textfield) VALUES ('test1')
SELECT @@IDENTITY AS NewUniqueID
</cfquery>

<cfset newid = insert.NewUniqueID>

No luck. After a little searching I found the solution. SQL Server doesn’t support returning a uniqueidentifier using the traditional methods.

New code:

<cfquery name="insert" datasource="#DSN#">
DECLARE @NewUniqueID uniqueidentifier
SET @NewUniqueID = NEWID()

INSERT INTO foo (uniqueid,textfield) VALUES (@NewUniqueID,'test1')
SELECT @NewUniqueID AS NewUniqueID
</cfquery>

<cfset newid = insert.NewUniqueID>