The amazing adventures of Doug Hughes

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#" />

Comments on: "From ColdFusion UUID to SQL UniqueIdentifier and Back Again" (4)

  1. Ryan Guill said:

    Those would be very helpful indeed. Just wondering though, how come you made them functions in mssql instead of a coldfusion udf?

    Like

  2. Doug Hughes said:

    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

    Like

  3. Doug Hughes said:

    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!

    Like

  4. Kunal Mehta said:

    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

    Like

Comments are closed.

Tag Cloud

%d bloggers like this: