Wednesday, December 14, 2011

Get returned value from Stored Procedure

This example shows you how to get returned value from Stored Procedure.First of all create a stored procedure which returns a value.In this example of stored procedure it receives a parameter named "uname".
Then its getting the Id of user from function fn_getUserBD_ID and returns that value.

Create PROCEDURE sp_GetBDID
@uname varchar(25)=null
AS
 declare @dbBD_id bigint
BEGIN
    SET NOCOUNT ON;
    Select @dbBD_id=dbo.fn_getUserBD_ID(@uname)
    return @dbBD_id
END

Now to get the value write this code in code behind
    public void GetBDID()
    {
        string st = Session["BDGen"].ToString();
        SqlCommand cmd = new SqlCommand("sp_GetBDID", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@uname", st);

        // Return value as parameter
        SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
        returnValue.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(returnValue);

        if (con.State == ConnectionState.Closed)
            con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        lblUId.Text = returnValue.Value.ToString();
    }

2 comments:

  1. Dear Anukana,

    Stored procedure never returns a value using return key word with variable

    ReplyDelete
  2. Dear Krishna,
    I think ur comment is not fully wrong bcoz Stored Procedure wont return Varchar values using return key but it will return int values.And do you know that all stored procedures have a return value, determined by the RETURN statement. The RETURN statement takes one optional argument, which should be a numeric value. If you say RETURN without providing a value, the return value is 0 if there is no error during execution. If an error occurs during execution of the procedure, the return value may be 0, or it may be a negative number. The same is true if there is no RETURN statement at all in the procedure: the return value may be a negative number or it may be 0.
    If you have still questions then follow the link "http://msdn.microsoft.com/en-us/library/ms174998.aspx".

    ReplyDelete