Wot no SCOPE_GUID() function??

Published 08 June 09 03:23 PM | MartinBell

I’ve talked about the problems of using GUIDs as key values, and some of the issues that you may come across in previous posts. But there are certain instances where GUIDs are necessary, such as for merge and transactional replication.

In those situations you may find yourself writing a stored procedure such as the example here by MVP Vyas. This is quite an old article, but it does point out there is no GUID equivalent of @@IDENTITY or in fact the is no system function like SCOPE_IDENTITY().

So how if you are not generating client side GUIDs how would you return these values?

The answer is to use the OUTPUT clause. Tony Rogerson blogged about how to use the output clause when inserting multiple identity values see here. This method can be used to return any defaulted or generated value including uniqueidentfier columns. For example if I am inserting a single order into my orders table I could use the following procedure:

  CREATE PROCEDURE usp_AddOrder ( @OrderGUID [uniqueidentifier] OUTPUT,
      @CustomerGUID [uniqueidentifier] =
NULL,
      @EmployeeID [uniqueidentifier] =
NULL,
      @OrderDate [datetime] =
NULL,
      @RequiredDate [datetime] =
NULL,
      @ShippedDate [datetime] =
NULL,
      @ShipName [nvarchar](40) =
NULL,
      @ShipAddress [nvarchar](60) =
NULL,
      @ShipCity [nvarchar](15) =
NULL,
      @ShipRegion [nvarchar](15) =
NULL,
      @ShipPostalCode [nvarchar](10) =
NULL,
      @ShipCountry [nvarchar](15) =
NULL
     
)
AS
BEGIN
      DECLARE @OrderGUIDs table( OrderGUID uniqueidentifier
);

     
BEGIN TRY
            BEGIN TRANSACTION
;
            INSERT INTO [dbo].
[Orders]
                           (
[CustomerGUID]
                           ,
[EmployeeID]
                           ,
[OrderDate]
                           ,
[RequiredDate]
                           ,
[ShippedDate]
                           ,
[ShipName]
                           ,
[ShipAddress]
                           ,
[ShipCity]
                           ,
[ShipRegion]
                           ,
[ShipPostalCode]
                           ,[ShipCountry]
)
                  OUTPUT INSERTED.OrderGUID INTO @OrderGUIDs
                  VALUES
                           (
@CustomerGUID
                           ,
@EmployeeID
                           ,
@OrderDate
                           ,
@RequiredDate
                           ,
@ShippedDate
                           ,
@ShipName
                           ,
@ShipAddress
                           ,
@ShipCity
                           ,
@ShipRegion
                           ,
@ShipPostalCode
                           ,
@ShipCountry
                       
);

           
SET @OrderGUID = (SELECT TOP 1 [OrderGUID] FROM @OrderGUIDs ) ; 
            COMMIT TRANSACTION
;
    END
TRY
    BEGIN CATCH
 

       
-- Print error information.
           
SELECT
                  ERROR_NUMBER() AS ErrorNumber,
                  ERROR_SEVERITY() AS ErrorSeverity,
                  ERROR_STATE() as ErrorState,
                  ERROR_PROCEDURE() as ErrorProcedure,
                  ERROR_LINE() as ErrorLine,
                  ERROR_MESSAGE() as ErrorMessage;
 

        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
      END CATCH ;
END ;
GO


A full script can be found here. If you add the ROWGUIDCOL property to the column, so that you can use the $ROWGUID keyword to reference the column name without having to know the actual column name. e.g.

CREATE PROCEDURE usp_AddOrder ( @OrderGUID [uniqueidentifier] OUTPUT,
      @CustomerGUID [uniqueidentifier] =
NULL,
      @EmployeeID [uniqueidentifier] =
NULL,
      @OrderDate [datetime] =
NULL,
      @RequiredDate [datetime] =
NULL,
      @ShippedDate [datetime] =
NULL,
      @ShipName [nvarchar](40) =
NULL,
      @ShipAddress [nvarchar](60) =
NULL,
      @ShipCity [nvarchar](15) =
NULL,
      @ShipRegion [nvarchar](15) =
NULL,
      @ShipPostalCode [nvarchar](10) =
NULL,
      @ShipCountry [nvarchar](15) =
NULL
     
)
AS
BEGIN
      DECLARE @OrderGUIDs table( OrderGUID uniqueidentifier
);

     
BEGIN TRY
            BEGIN TRANSACTION
;
            INSERT INTO [dbo].
[Orders]
                           (
[CustomerGUID]
                           ,
[EmployeeID]
                           ,
[OrderDate]
                           ,
[RequiredDate]
                           ,
[ShippedDate]
                           ,
[ShipName]
                           ,
[ShipAddress]
                           ,
[ShipCity]
                           ,
[ShipRegion]
                           ,
[ShipPostalCode]
                           ,[ShipCountry]
)
                  OUTPUT INSERTED.$ROWGUID INTO
@OrderGUIDs
                 
VALUES
                           (
@CustomerGUID
                           ,
@EmployeeID
                           ,
@OrderDate
                           ,
@RequiredDate
                           ,
@ShippedDate
                           ,
@ShipName
                           ,
@ShipAddress
                           ,
@ShipCity
                           ,
@ShipRegion
                           ,
@ShipPostalCode
                           ,
@ShipCountry
                       
);

           
SET @OrderGUID = (SELECT TOP 1 [OrderGUID] FROM @OrderGUIDs ) ; 
            COMMIT TRANSACTION
;
    END
TRY
    BEGIN CATCH
 

       
-- Print error information.
           
SELECT
      
            ERROR_NUMBER() AS ErrorNumber
,
                  ERROR_SEVERITY() AS ErrorSeverity
,
                  ERROR_STATE() as ErrorState
,
                  ERROR_PROCEDURE() as ErrorProcedure
,
                  ERROR_LINE() as ErrorLine
,
                  ERROR_MESSAGE() as ErrorMessage
;
 

        IF XACT_STATE() <> 0
            ROLLBACK
TRANSACTION;
      END CATCH
;
END
;
GO

A modified script can be found hereTo run ether of these samples to observe what happens use:

 

DECLARE @OrderGUID uniqueidentifier
EXEC usp_AddOrder @OrderGUID OUTPUT
SELECT @OrderGUID
SELECT * FROM [dbo].[Orders]
GO

DECLARE @OrderGUID uniqueidentifier
EXEC usp_AddOrder @OrderGUID OUTPUT
SELECT @OrderGUID
SELECT * FROM [dbo].[Orders]
GO

Of course if you need to use a SCOPE_GUID function. then you should probably be using an IDENTITY column!!

Comments

# Dew Drop - June 9, 2009 | Alvin Ashcraft's Morning Dew said on June 9, 2009 02:03 PM:

Pingback from  Dew Drop - June 9, 2009 | Alvin Ashcraft's Morning Dew

# Martin Bell UK SQL Server MVP said on June 14, 2009 05:39 PM:

In my previous posts I have looked at using the OUTPUT clause to return GUID values generated by the database and how you can return these to an ADO.Net application but this doesn&#39;t really give an indication of the power and usefulness that clause

# Martin Bell UK SQL Server MVP said on May 3, 2011 10:55 PM:

Triggers can effect the value returned by @@INDENTITY and the common advice is to say that yoiu should always use SCOPE_IDENITITY, the post proces that that is not always good advice.

# Martin Bell UK SQL Server MVP said on November 4, 2011 08:51 PM:

My previous post showed how to return a database generated GUID as a stored procedure parameter, but if more than one GUID is generated what do you do?

This Blog

SQL Blogs

Syndication