what is the best way to return a auto created guid id?

Question

I have a table that has a newid() for the primary key field (Id)

in the stored procedure i need to return this newly created guid back as a return parameter, i could create the guid beforehand and insert into the database, but that would defeat the purpose of a newid(). Is there a good way to handle this?

INSERT INTO [SUBS].[dbo].[Subscriptions]
           ([CustomerId]
           ,[SubscriptionClassId]
           ,[StartDate]
           ,[TrialAmount]
           ,[Amount]
           ,[TrialInterval]
           ,[Interval]
           ,[TrialIntervalLength]
           ,[IntervalLength]
           ,[CreatedBy]
           ,[LastUpdatedBy])
     VALUES
           (@CustomerId
           ,@SubscriptionClassId
           ,@StartDate
           ,@TrialAmount
           ,@Amount
           ,@TrialInterval
           ,@Interval
           ,@TrialIntervalLength
           ,@IntervalLength
           ,@CreatedBy
           ,@CreatedBy)

select @SubscriptionId = SCOPE_IDENTITY() -- this does not work because scope_identity is numeric

Answer 1

I haven't actually tested this, but if you're using a later version of SQL server you can use output statements:

DECLARE @ins TABLE (id uniqueidentifier)

INSERT INTO [SUBS].[dbo].[Subscriptions] 
           ([CustomerId] 
           ,[SubscriptionClassId] 
           ,[StartDate] 
           ,[TrialAmount] 
           ,[Amount] 
           ,[TrialInterval] 
           ,[Interval] 
           ,[TrialIntervalLength] 
           ,[IntervalLength] 
           ,[CreatedBy] 
           ,[LastUpdatedBy]) 
     OUTPUT INSERTED.id INTO @ins (id)
     VALUES 
           (@CustomerId 
           ,@SubscriptionClassId 
           ,@StartDate 
           ,@TrialAmount 
           ,@Amount 
           ,@TrialInterval 
           ,@Interval 
           ,@TrialIntervalLength 
           ,@IntervalLength 
           ,@CreatedBy 
           ,@CreatedBy) 

select @SubscriptionId = id from @ins

Otherwise you'd need to create an identity column on the subscriptions table and then use that as a lookup to select the id out.

Answer 2

The output of newid() can be casted to a string value.

Use CAST(NEWID() AS varchar(36)) to get a GUID string.