SQL Server: Return uniqueidentifier from stored procedure

Can I return UNIQUEIDENTIFIER from a stored procedure using the RETURN statement or is it only by using the OUTPUT statement?

i.e to return the PersonID UNIQUEIDENTIFIER:

CREATE PROCEDURE CreatePerson
    @Name NVARCHAR(255), 
    @Desc TEXT
AS
DEC开发者_JAVA百科LARE @Count INT
DECLARE @JobFileGUID UNIQUEIDENTIFIER 

-- Check if job exists?
SET @Count = (SELECT COUNT(Name) AS Name FROM Person WHERE Name=@Name)

IF @Count < 1
BEGIN
    SET @PersonGUID = NEWID();

    INSERT INTO Person 
        (PersonID, Name, [Desc]) 
        VALUES (@PersonGUID, @Name, @Desc)

END

    SELECT @PersonGUID = Person.PersonID 
    FROM Person
    WHERE Name = @Name

    RETURN @PersonGUID
GO

Thanks

In stored procedure - only using the OUTPUT statement. In function - return.

Use:

CREATE PROCEDURE CreatePerson
    @Name NVARCHAR(255), 
    @Desc TEXT,
    @PersonGUID UNIQUEIDENTIFIER OUTPUT
AS
BEGIN

   SET @PersonGUID = ...

END

How to call:

DECLARE 
    @name NVARCHAR(255),
    @desc TEXT,
    @personGUID UNIQUEIDENTIFIER

SET @name = 'Bob'
SET @desc = 'One handsome man.'

EXEC [Database].[schema].CreatePerson @name, @desc, @personGUID OUTPUT

From the documentation you can actually see that a return in a stored procedure is actually used as a response code, hence you get the exception when trying to return a uniqueidentifier.

https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-ver16#return-data-using-a-return-code

How I solved it, is by just performing a SELECT after the insert of the generated unique identifier.

DECLARE @ReportId UNIQUEIDENTIFIER;
SET @ReportId = NEWID();

INSERT INTO [dbo].[Report]
           ([ReportId]
           ,[ReportName])
     VALUES
           (@ReportId
           ,@ReportName)

SELECT @ReportId as ReportIdInternal

You'll have to see how to perform that with multiple selects though.

CREATE TABLE [dbo].[tbl_Clients]( [ClientID] [uniqueidentifier] NULL, [ClientName] varchar NULL, [ClientEnabled] [bit] NULL ) ON [PRIMARY]

GO

CREATE PROCEDURE [dbo].[sp_ClientCreate] @in_ClientName varchar(250) = "New Client 123", @in_ClientEnabled bit, @out_ClientId uniqueidentifier OUTPUT AS

SET @out_ClientId = NEWID();

INSERT INTO tbl_Clients(ClientId, ClientName, ClientEnabled) VALUES( @out_ClientId, @in_ClientName, @in_ClientEnabled)

DECLARE @return_value int, @out_ClientId uniqueidentifier

EXEC @return_value = [dbo].[sp_ClientCreate] @in_ClientName = N'111', @in_ClientEnabled = 1, @out_ClientId = @out_ClientId OUTPUT

SELECT @out_ClientId as N'@out_ClientId'

SELECT 'Return Value' = @return_value

GO

Result:-59A6D7FE-8C9A-4ED3-8FC6-31A989CCC8DB