none
How does OUTPUT Parameter works in STORED PROCEDURES RRS feed

  • Question

  • Dear readers,I can't understand HOW  to create a  OUTPUT parameter in stored procedure and make it work. I was practicing with this e.g below. but is not running well. Can someone run me the steps to create with a explanation.

    Regards!! SEE PART A OF THE T_SQL FOR CREATING VARIABLES, I HOPE I DID IT RIGHT!

                          Create procedure Avg_sum
                          (
                                  @sum1 smallint,
                                  @sum2 smallint,
                                  @sum3 smallint,
                                  @dog  smallint Output
                            )
                                   AS
                                         Select @dog= (@sum1+@sum2+@sum3)/3
                                         GO

    FIRST I EXECUTED THE ABOVE: LET's NAME IT PART A,  AND IT EXECUTED SUCCESSFULLY

    NOW I TYPED THE FOLLOWING: Let's Call it PART B

                                         1)  Declare @mouse smallint

                                         2) Execute Avg_sum 2,2,2, @mouse Output
                                         3) Select 'The average is', @mouse

    I RAN THE ABOVE 3 STATEMENTS TOGETHER in PART B: BUT doesn't run well

    THANK-YOU!!



    • Edited by SQL75 Monday, October 13, 2014 5:54 PM
    Monday, October 13, 2014 5:50 PM

Answers

All replies

  • It ran OK for me. Just remove 1)  2)  3) and run it together.

    See example: http://www.sqlusa.com/bestpractices2008/stored-procedure-parameters/




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    Monday, October 13, 2014 6:00 PM
    Moderator
  • I don't see any problems and was able the run the scripts with no problems, returning a value of 2.  Exactly what do you mean by "doesn't run well"?  Below is the script I ran.

    CREATE PROCEDURE Avg_sum
        (
          @sum1 smallint
        , @sum2 smallint
        , @sum3 smallint
        , @dog smallint OUTPUT
        )
    AS
        SELECT  @dog = ( @sum1 + @sum2 + @sum3 ) / 3
    GO
    
    DECLARE @mouse smallint
    
    EXECUTE Avg_sum 2, 2, 2, @mouse OUTPUT
    SELECT  'The average is'
          , @mouse
    GO


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    Monday, October 13, 2014 6:04 PM
  • Yes, it doesn't have any issues, what is the error you are getting

    Create procedure Avg_sum
                          (
                                  @sum1 smallint,
                                  @sum2 smallint,
                                  @sum3 smallint,
                                  @dog  smallint Output
                            )
                                   AS
                                         Select @dog= (@sum1+@sum2+@sum3)/3
                                         GO
           
           Declare @mouse smallint  
           Execute Avg_sum @sum1=2,@sum2=2,@sum3=2,@dog= @mouse Output
           Select 'The average is', @mouse 

    • Marked as answer by SQL75 Monday, October 13, 2014 6:30 PM
    Monday, October 13, 2014 6:10 PM
  • I am not sure what is happening on my screen, but let me try again on a new query windows. The error I was getting was

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

    But let me run on a new query windows 1,2,3 of Part B together.

    Monday, October 13, 2014 6:29 PM
  • Sorry must be some previous synax, it is running fine on a new query window.  Basically the

     Delcare @mouse smallint             is a variable to park the OUTPUT right?

    ThanksSQL75


    • Edited by SQL75 Monday, October 13, 2014 6:50 PM
    Monday, October 13, 2014 6:32 PM
  • Thanks Dan it was some stupid syntax error, I ran into the new window and it runs fine!!

    RegardsSQL75

    Monday, October 13, 2014 6:36 PM