CODEWITHSUNDEEP
sql-serverstored-procedures
Sean Kelly
Sean Kelly

Reputation: 991

How to get a unique identifier from a stored procedure?

I feel like I'm so close I just can't seem to figure out how to get a GUID from SQL Server with my stored procedure. It's either not returning the value or I'm not calling my output the right way.

After looking at documentation for 2 days, this is what I'm come up with:

DROP PROCEDURE IF EXISTS create_product
GO

CREATE PROCEDURE create_product 
    (@Name VARCHAR(255),
     @Price DOUBLE PRECISION,
     @Image VARCHAR(255),
     @Free_Shipping BIT,
     @Description VARCHAR(MAX),
     @Shipping_Id UNIQUEIDENTIFIER,
     @PRODUCT_ID UNIQUEIDENTIFIER OUTPUT)
AS
    SELECT @PRODUCT_ID = NEWID();

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

    INSERT INTO Product (product_id, product_name, product_price, product_image,
                         product_free_shipping, product_detail_id) 
    VALUES (@PRODUCT_ID, @Name, @Price, @Image,
            @Free_Shipping, NULL);

    INSERT INTO ProductDetail (detail_id, detail_description, detail_product_id,
                               detail_product_meta_id, detail_shipping_id)
    VALUES (@PRODUCT_DETAIL_ID, @Description, @PRODUCT_ID,
            NULL, @Shipping_Id);

    UPDATE Product
    SET product_detail_id = @PRODUCT_DETAIL_ID
    WHERE product_id = @PRODUCT_ID;

    PRINT @PRODUCT_ID
GO

DECLARE @PID UNIQUEIDENTIFIER;

EXEC create_product 'Name8', 10.79, 'Image8', 0, 'Description of the eigth', '0511DE6D-F448-471D-B1B2-11C19E991927', @PID OUTPUT
GO

PRINT @PID

I only added the PRINT at the end for debugging purposes to make sure that my value is indeed being set. The error that I get is:

Msg 137, Level 15, State 2, Line 60
Must declare the scalar variable "@PID".

Which seems weird

Upvotes: 1

Views: 1329

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88996

Variables are scoped to the batch, and GO is the batch separator. Remove it.

DECLARE @PID UNIQUEIDENTIFIER;
EXEC create_product 'Name8', 10.79, 'Image8', 0, 'Description of the eigth', '0511DE6D-F448-471D-B1B2-11C19E991927', @PID OUTPUT
PRINT @PID

Upvotes: 5

Related Questions