Returning data from a stored procedure in the SQL Server

The following code shows how to use  the RETURN statement to return data from a stored procedure in the SQL Server.

The sytax of the stored procedure looks as below:

CREATE PROC dbo.TestReturn (@InValue int)
AS
Return @Invalue
GO

All it does is use the RETURN statement to send back the value that was passed in. Note that executing a RETURN statement causes a stored procedure to stop executing and return control back to the calling program. This is often used to test for error conditions and stop processing if one is found. The following script calls the TestReturn stored procedure:

DECLARE @ReturnValue INT
EXEC @ReturnValue = TestReturn 3
SELECT ReturnValue=@ReturnValue

and the output looks like this:

ReturnValue 
----------- 
3

(1 row(s) affected)

Whatever number is returned using the RETURN statement is put into the variable @ReturnValue.  The ASP.NET code to get the return value looks like this:

// Your code should get the connection string from web.config
string connectionString = 
  @"Server=.\SQLExpress; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("dbo.TestReturn"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@Invalue", 3));

        SqlParameter returnValue = new SqlParameter("@Return_Value", DbType.Int32);
        returnValue.Direction = ParameterDirection.ReturnValue;

        cmd.Parameters.Add(returnValue);

        conn.Open();
        cmd.Connection = conn;

        cmd.ExecuteNonQuery();
        int count = Int32.Parse(cmd.Parameters["@Return_Value"].Value.ToString());
        Response.Write("<p>Return Code: " + count.ToString());
        conn.Close();
    }
}

It only returns a single numeric value. This is most commonly used to return a status result or error code from a procedure.