Wot no SCOPE_GUID() function??
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 here. To 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!!