CREATE DATABASE GUIDTEST;
GO

USE GUIDTEST;
GO

CREATE TABLE [dbo].[Orders] (
	[OrderGUID] [uniqueidentifier] NOT NULL CONSTRAINT DF_OrderGUID DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
	[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
	CONSTRAINT [PK_Orders] PRIMARY KEY  CLUSTERED 
	(
		[OrderGUID]
	)  
) ;
GO

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

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