I had a little time today and wrote a couple of handy (and very simple) Microsoft SQL functions for converting ColdFusion UUIDs to the SQL UniqueIdentifier type and vice versa.
ColdFusion UUID to SQL UniqueIdentifier:
Pass this function any ColdFusion created UUID and it will be “converted” to be a SQL UniqueIdentifier.
CREATE FUNCTION CF2SQLUUID
(
@guid AS varchar(35)
)
RETURNS uniqueidentifier
AS
BEGIN
RETURN CONVERT(uniqueidentifier, LEFT(@guid, 23) + '-' + RIGHT(@guid, 12))
END
Example Usage:
<cfquery name="getUUID" datasource="myDSN">
SELECT dbo.CF2SQLUUID('#createUUID()#') as sqlid
</cfquery>
<cfdump var="#getUUID.sqlid#" />
SQL UniqueIdentifier to ColdFusion UUID:
Pass this function any SQL created UniqueIdentifier and it will be “converted” to be a Coldfusion UUID.
CREATE FUNCTION SQL2CFUUID
(
@guid AS uniqueidentifier
)
RETURNS varchar(35)
AS
BEGIN
RETURN LEFT(CONVERT(varchar(36), @guid), 23) + RIGHT(CONVERT(varchar(36), @guid), 12)
END
Example Usage:
<cfquery name="getUUID" datasource="myDSN">
SELECT dbo.SQL2CFUUID(newID()) as cfid
</cfquery>
<cfdump var="#getUUID.cfid#" />
DougHughes.net 
Comments on: "From ColdFusion UUID to SQL UniqueIdentifier and Back Again" (4)
Those would be very helpful indeed. Just wondering though, how come you made them functions in mssql instead of a coldfusion udf?
LikeLike
Because I wanted to create UUIDs in ColdFusion (which is typeless), but be able to hand them to SQL to store in a column of type UniqueIdentifier without any additional CF jockeying. If I implemented this in CF then would be a little more work involved. Plus, I can just stick these two functions in the master DB and use them whenever and whenever I want. š
I guess, in general, it’s a matter of preference.
Doug
LikeLike
Well, I’m a little embarased, I totaly screwed up the SQL2CFUUID function.
Rather than converting the UUID to a string, then getting the left and right portions of the string, I was getting the left and right portions of the UniqueIdentifuer, concatinating them and then converting that to a string. Without getting technical or anything, it doesn’t work that way.
I’m not sure how that snuck past QA!
I’ve updated the function and it now works correctly. Oops!
LikeLike
Please HELP me š
=============================
spOPPORTUNITIES_Update
Convert(uniqueidentifier,’00000000-0000-0000-0000-000000000000′)
, Cast(‘00000000-0000-0000-0000-000000000001’ as uniqueidentifier)
, Cast(‘ce7fec42-7b49-42d0-a245-ae90975e634a’ as uniqueidentifier)
, ‘Club Membership’
, ‘
, ‘Cold Call’
, Cast(0 as decimal)
, Cast(‘00000000-0000-0000-0000-000000000000′ as uniqueidentifier)
, Cast(’01/01/0001 12:00:00 AM’ as datetime)
, ‘
, ‘
, Cast(0.0 as float)
, ‘Member Prospect (Smith Williams) Interested in Product 3’
, ‘Member Prospect’
, Cast(‘7e57e44b-5a94-4698-8a74-1a0f7a455196’ as uniqueidentifier)
, ‘test’
, Cast(’62ef766f-2697-4ac1-b029-6fa524a6749d’ as uniqueidentifier)
=============================
exec above proc but getting error message below:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘Convert’.
————————–
Kunal Mehta
http://360by2.blogspot.com
LikeLike