none
Stored Procedure with and without OUTPUT parameter RRS feed

  • Question

  • Let's say I have a table called T_Portfolio that has columns PortfolioID (int) and PortfolioName (varchar(50)).

    What is the difference between the stored procs created by the following Create Procedure statements?

    Proc1:

    create procedure getPortfolio
    @ID int
    ,@Name varchar(50) output
    as
    select @Name = PortfolioName from T_Portfolio where PortfolioID = @ID;

    Proc2:

    --same as Proc1 but without the output option

    create procedure getPortfolio
    @ID int
    ,@Name varchar(50)
    as
    select @Name = PortfolioName from T_Portfolio where PortfolioID = @ID;

    Now, I know how Proc1 works. That is, I would call it like this.

    declare @somevariable varchar(50)
    exec getPortfolio <PortfolioPK>, @somevariable output
    select @somevariable

    Then this will display the PortfolioName associated with <PortfolioPK> AND assign that value to the variable @somevariable.

    However, in the case of Proc2 (that was created without the output option), I am not sure what the use would be.
    For example, I see that a statement such as the following works without an error.

    exec getPortfolio <PortfolioPK>, XXXX

    But what is happening with XXXX parameter that I passed into Proc2?

    Friday, August 28, 2009 5:26 PM

Answers

  • when you use OUTPUT Parameter in the sp the sp returns the data back to the calling application

    if you dont put output   word with the parameter SQL Server takes this parameter as ordinary parmeter

    >>http://msdn.microsoft.com/en-us/library/ms378108(SQL.90).aspx
    the answer is it will be unused and the parameter variable will be over written  in the next stament

    check this link if you want more information about output parameters

    http://msdn.microsoft.com/en-us/library/ms378108(SQL.90).aspx
    • Marked as answer by DoolinDalton Friday, August 28, 2009 7:29 PM
    Friday, August 28, 2009 5:37 PM

All replies

  • This is a design flaw. You used random naming conventions instead of ISO metadata standards. ID is not a valid key in RDBMS except in kabbalah DBMS.

    Stored procedures return/accept sets only, not scalars.
    Friday, August 28, 2009 5:30 PM
  • when you use OUTPUT Parameter in the sp the sp returns the data back to the calling application

    if you dont put output   word with the parameter SQL Server takes this parameter as ordinary parmeter

    >>http://msdn.microsoft.com/en-us/library/ms378108(SQL.90).aspx
    the answer is it will be unused and the parameter variable will be over written  in the next stament

    check this link if you want more information about output parameters

    http://msdn.microsoft.com/en-us/library/ms378108(SQL.90).aspx
    • Marked as answer by DoolinDalton Friday, August 28, 2009 7:29 PM
    Friday, August 28, 2009 5:37 PM

  • Stored procedures return/accept sets only, not scalars.

    you have to prove your statement with some refrences
    Friday, August 28, 2009 5:39 PM
  • when you use OUTPUT Parameter in the sp the sp returns the data back to the calling application

    if you dont put output   word with the parameter SQL Server takes this parameter as ordinary parmeter

    >>http://msdn.microsoft.com/en-us/library/ms378108(SQL.90).aspx
    the answer is it will be unused and the parameter variable will be over written  in the next stament

    check this link if you want more information about output parameters

    http://msdn.microsoft.com/en-us/library/ms378108(SQL.90).aspx

    After thinking about it a bit more, I think I understand what Proc2 in my initial example is doing.
    Friday, August 28, 2009 6:45 PM
  • you can refer this example : http://www.varindersandhu.in/2011/11/17/sql-server-stored-procedure-with-output-parameters/
    If you think my suggestion is useful, please rate it as helpful.

    If it has helped you to resolve the problem, please Mark it as Answer.

    Varinder Sandhu www.varindersandhu.in
    Wednesday, November 23, 2011 6:07 AM