none
How to get the return value from a stored procedure

    Question

  • Hi everyone..

    I've a stored procedure and procedure works well. In that procedure, there is one output parameter which i need to get returned.

    this is my procedure..

    ALTER PROCEDURE [dbo].[tgms_reclass_PO_bip](@po VARCHAR(20), @old_gl VARCHAR(20), @new_gl VARCHAR(20),@output varchar(500) out )
    AS


    IF (SELECT COUNT(*) FROM mm2po WHERE scode = @po) = 0
    BEGIN
        --SET @output = '1'
        SET @output = 'Invalid PO Number'
    END



    /*****************************
    Check if Old GL Code is valid
    ******************************/
    IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@old_gl,'-','')) = 0)
    BEGIN
        --SET @output = '2'
        SET @output = 'Invalid Old GL Account'
    END



    /*****************************
    Check if New GL Code is valid
    ******************************/
    IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@new_gl,'-','')) = 0)
    BEGIN
        --SET @output = '3'
        SET @output = 'Invalid New GL Account'
    END



    /******************************************
    Check if the PO has been invoiced or paid
    *******************************************/
    IF (@output = '' AND (SELECT COUNT(*) FROM mm2podet pd INNER JOIN detail d ON d.hpodet = pd.hmy AND pd.hpo = @po) <> 0)
    BEGIN
        --SET @output = '4'
        SET @output = 'PO cannot be reclassed, it has been Invoiced/Paid'
    END


    /***********************************************
    Update PO details, show output
    ***********************************************/
    IF @output = ''
        BEGIN
        UPDATE    pd
        SET        hpayacct = a1.hmy
        --SELECT    *
        FROM    mm2podet pd
        INNER JOIN
                acct a
        ON        a.hmy = pd.hpayacct
        AND        a.scode = REPLACE(@old_gl,'-','')
        AND        pd.hpo = @po
        INNER JOIN
                acct a1
        ON        a1.scode = REPLACE(@new_gl,'-','')

        IF @@rowcount = 0
        BEGIN
            --SET @output = '5'
            SET @output = 'PO cannot be reclassed, please check the PO/Old GL combination'
        END
        ELSE
        BEGIN
            --SET @output = '6'
            SET @output = 'PO# ' + @po + ' has been reclassed from ' + @old_gl + ' to ' + @new_gl
        END

    END

    SELECT @output as output

     

    How can i get @output in my code behind.??

    Please help?!!!!!

    • Moved by SamAgain Friday, September 24, 2010 2:30 AM not 64bit .net related (From:64-Bit .NET Framework Development.)
    • Moved by Ai-hua Qiu Monday, September 27, 2010 10:47 AM (From:Transact-SQL)
    Thursday, September 23, 2010 3:05 PM

Answers

All replies

  • Hi everyone..

    I've a stored procedure and procedure works well. In that procedure, there is one output parameter which i need to get returned.

    this is my procedure..

    ALTER PROCEDURE [dbo].[tgms_reclass_PO_bip](@po VARCHAR(20), @old_gl VARCHAR(20), @new_gl VARCHAR(20),@output varchar(500) out )
    AS


    IF (SELECT COUNT(*) FROM mm2po WHERE scode = @po) = 0
    BEGIN
        --SET @output = '1'
        SET @output = 'Invalid PO Number'
    END



    /*****************************
    Check if Old GL Code is valid
    ******************************/
    IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@old_gl,'-','')) = 0)
    BEGIN
        --SET @output = '2'
        SET @output = 'Invalid Old GL Account'
    END



    /*****************************
    Check if New GL Code is valid
    ******************************/
    IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@new_gl,'-','')) = 0)
    BEGIN
        --SET @output = '3'
        SET @output = 'Invalid New GL Account'
    END



    /******************************************
    Check if the PO has been invoiced or paid
    *******************************************/
    IF (@output = '' AND (SELECT COUNT(*) FROM mm2podet pd INNER JOIN detail d ON d.hpodet = pd.hmy AND pd.hpo = @po) <> 0)
    BEGIN
        --SET @output = '4'
        SET @output = 'PO cannot be reclassed, it has been Invoiced/Paid'
    END


    /***********************************************
    Update PO details, show output
    ***********************************************/
    IF @output = ''
        BEGIN
        UPDATE    pd
        SET        hpayacct = a1.hmy
        --SELECT    *
        FROM    mm2podet pd
        INNER JOIN
                acct a
        ON        a.hmy = pd.hpayacct
        AND        a.scode = REPLACE(@old_gl,'-','')
        AND        pd.hpo = @po
        INNER JOIN
                acct a1
        ON        a1.scode = REPLACE(@new_gl,'-','')

        IF @@rowcount = 0
        BEGIN
            --SET @output = '5'
            SET @output = 'PO cannot be reclassed, please check the PO/Old GL combination'
        END
        ELSE
        BEGIN
            --SET @output = '6'
            SET @output = 'PO# ' + @po + ' has been reclassed from ' + @old_gl + ' to ' + @new_gl
        END

    END

    SELECT @output as output

     

    How can i get @output in my code behind.??

    Please help?!!!!!

    • Merged by Ai-hua Qiu Monday, September 27, 2010 11:05 AM
    Thursday, September 23, 2010 3:22 PM
  • declare @MyOutParam varchar(50)
    
    exec MyProc @MyOutParam out
    
    select @MyOutParam
    
    

    http://www.t-sql.ru
    Thursday, September 23, 2010 3:33 PM
  • Use the output clause when you call it.  For example,

    Declare @Result varchar(500);
    Exec dbo.tgms_reclass_PO_bip 'Abc', 'Def', @Result OUTPUT;
    

    Then the value will be returned.

    Tom

    Thursday, September 23, 2010 3:37 PM
  • I'm getting return value in the sql, but i want to get the value to my code behind. ???
    Thursday, September 23, 2010 3:39 PM
  • if return value integer:

    create procedure MyProc
    ( @val int )
    as
    if @val%2 = 0
    return 0
    else
    return 1
    go
    
    declare @i int
    exec @i = MyProc 11
    select @i
    

    http://www.t-sql.ru
    Thursday, September 23, 2010 3:42 PM
  • I'm getting return value in the sql, but i want to get the value to my code behind. ???

    mY CODE IS THIS:

    SqlConnection con = new SqlConnection("Data Source=YSQL6;Initial Catalog=tgms_live6_daily;User ID=sa;Password=paraluman;");
            SqlCommand cmd = new SqlCommand();

    cmd.Connection = con;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@po", txtPONumber.Text.Trim());
                cmd.Parameters.AddWithValue("@old_gl", txtOldGl.Text.Trim());
                cmd.Parameters.AddWithValue("@new_gl", txtNewGl.Text.Trim());
                SqlParameter op = new SqlParameter("@output", SqlDbType.VarChar);
                op.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(op);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.tgms_reclass_PO_bip";
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
                string ss = op.Value.ToString();

    Thursday, September 23, 2010 4:01 PM
  • Check out the following links:

    Examples of using System.Data.ParameterDirection with Sql Server

    The "ins" and "outs" of Using Stored Procedures in C#

    Consider also posting on developer forums.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by Ai-hua Qiu Friday, October 01, 2010 9:27 AM
    Friday, September 24, 2010 4:28 PM
  • I guess You need a Open Connection to Read the value of the Output Parameter.

          con.Open();
          cmd.ExecuteNonQuery();
            //Read the value then close the connection
          string ss = op.Value.ToString();

          con.Close();

     

    Thanks.

     


    We are born to WORK and PROSPER not to rest and rust.
    Friday, September 24, 2010 7:00 PM
  • Hi

    What kind of technology are you using behind, VB.Net or C#. Also are you using LINQ or Ado.Net?

    Friday, September 24, 2010 7:40 PM
  • Hi

     

    Change Select @output as output (this is returning output) to

    Select @output = 'PO# ' + @po + ' has been reclassed from ' + @old_gl + ' to ' + @new_gl

     You C# code looks fine...

    That should do it

    Friday, September 24, 2010 7:50 PM
  • Hi,

    We can call the Parameterized Stored Procedure by Using ADO.NET, please refer to the following link:

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/baf09902-3a0a-4f33-abaf-171b384d96ee

     

    Thanks,

    Ai-Hua Qiu


    Constant dropping wears away a stone.
    Monday, September 27, 2010 10:57 AM
  • Hi,

     

    Public String Method1 ()

    {

    SqlConnection con = new SqlConnection("Data Source=YSQL6;Initial Catalog=tgms_live6_daily;User ID=sa;Password=paraluman;");
            SqlCommand cmd = new SqlCommand();

    cmd.Connection = con;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@po", txtPONumber.Text.Trim());
                cmd.Parameters.AddWithValue("@old_gl", txtOldGl.Text.Trim());
                cmd.Parameters.AddWithValue("@new_gl", txtNewGl.Text.Trim());
                SqlParameter op = new SqlParameter("@output", SqlDbType.VarChar);
                op.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(op);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.tgms_reclass_PO_bip";
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();


                return op.Value.ToString();

    }

    call the functio to store in another variable

    String a=Method1();


    PS.Shakeer Hussain Hyderabad
    Monday, September 27, 2010 11:13 AM