Skip to: Site menu | Main content

Email LinkedIn

Blog...

Returning a uniqueidentifier from MS SQL Server in Coldfusion.

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

Table structure:

UniqueIDuniqueidentifier
TextFieldvarchar(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>

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)