OUTPUT parameter - I understood its meaning correctly?!

  • nhatnghe39

    SSC Journeyman

    Points: 75


    CREATE PROC [dbo].[spInsertOrder]
    @OrderID INT OUTPUT
    AS
       INSERT INTO dbo.Orders
        ...
    SELECT @OrderID = @@IDENTITY

    With above snippet, sql server will find last order id, saved it into @@IDENTITY, after assigns that value to @OrderID 


    DECLARE @MyIdent INT 
    EXEC [dbo].[spInsertOrder] 
              ...
              @OrderID = @MyIdent OUTPUT

    SELECT * FROM dbo.Orders
    WHERE OrderID = @MyIdent

    For this snippet, I should understand that @OrderID was storing value from sproc, after push it to @MyIdent or @MyIdent was storing value of @OrderID (sproc), when execute it'll assign value to @OrderID?

    Thanks for your reading, any ideas ...

  • DesNorton

    SSC-Insane

    Points: 22550

    nhatnghe39 - Wednesday, April 25, 2018 12:05 AM


    CREATE PROC [dbo].[spInsertOrder]
    @OrderID INT OUTPUT
    AS
       INSERT INTO dbo.Orders
        ...
    SELECT @OrderID = @@IDENTITY

    With above snippet, sql server will find last order id, saved it into @@IDENTITY, after assigns that value to @OrderID 


    DECLARE @MyIdent INT 
    EXEC [dbo].[spInsertOrder] 
              ...
              @OrderID = @MyIdent OUTPUT

    SELECT * FROM dbo.Orders
    WHERE OrderID = @MyIdent

    For this snippet, I should understand that @OrderID was storing value from sproc, after push it to @MyIdent or @MyIdent was storing value of @OrderID (sproc), when execute it'll assign value to @OrderID?

    Thanks for your reading, any ideas ...

    Your understanding is correct in this case.
    HOWEVER, be vary careful with @@IDENTITY.  It is not guaranteed to give you YOUR latest inserted identity value.  It could give you the latest inserted identity value from any session.  Rather use SCOPE_IDENTITY

  • nhatnghe39

    SSC Journeyman

    Points: 75

    Well, I only wonder why we can assign @MyIdent to @OrderID right away after declaring it, maybe I will assume that @OrderID passed out from sproc to execute, when run this line:


    @OrderID = @MyIdent OUTPUT

    SQL Server will assign value from @OrderID to @MyIdent...

  • DesNorton

    SSC-Insane

    Points: 22550

    nhatnghe39 - Wednesday, April 25, 2018 12:41 AM

    Well, I only wonder why we can assign @MyIdent to @OrderID right away after declaring it, maybe I will assume that @OrderID passed out from sproc to execute, when run this line:


    @OrderID = @MyIdent OUTPUT

    SQL Server will assign value from @OrderID to @MyIdent...

    Before calling proc
    1 - Value of @MyIdent is assigned to @OrderID, and is available for use in the proc.  As it is an OUTPUT param, it can be changed by the proc.

    In Proc ...
    1 - INSERT into table
    2 - Table IDENTITY increases
    3 - @@IDENTITY is assigned latest IDENTITY value
    4 - @@IDENTITY value assigned to @OrderID, and passed out

    After calling proc
    1 - Value of @OrderID is assigned to @MyIdent

    Below is a temp proc that shows how the value can be used, and changed.
    CREATE PROCEDURE #TestOutParam
    @ParamVal INT OUTPUT
    AS
    BEGIN
    SELECT InputParamVal = @ParamVal;
    SET @ParamVal += 10;
    SELECT OutputParamVal = @ParamVal;
    END;
    GO

    DECLARE @MyVal INT = 5;

    EXEC #TestOutParam @ParamVal = @MyVal OUTPUT;

    SELECT MyVal = @MyVal;
    GO

    DROP PROCEDURE dbo.#TestOutParam;
    GO

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply