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:
| 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>
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>
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>
DECLARE @NewUniqueID uniqueidentifier
SET @NewUniqueID = NEWID()
INSERT INTO foo (uniqueid,textfield) VALUES (@NewUniqueID,'test1')
SELECT @NewUniqueID AS NewUniqueID
</cfquery>
<cfset newid = insert.NewUniqueID>
There are no comments for this entry.
[Add Comment]