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:

UniqueID uniqueidentifier
TextField varchar(50)

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>

0 Comments

Leave Your Comment

Your email address will not be published.


about me

An information technology professional with twenty two year's experience in systems administration, computer programming, requirements gathering, customer service, and technical support.