Stored Procedures and Return Statement

In a recent interview i was been asked whether stored procedures can have “return” keyword and what type of values it can return.

I thought i will note this point here.

Stored Procedures can return value. Only a single numeric value can be returned.
Consider the following snippet.

[code lang=”sql”]

CREATE PROCEDURE dbo.GetCustomerMaxID ()
AS

DECLARE @MAXID INT

SELECT MAX(ID) FROM CUSTOMER

Return @MAXID

[/code]

and we can call the proceduce like this

[code lang=”sql”]

DECLARE @ReturnValue INT
EXEC @ReturnValue = GetCustomerMaxID
SELECT ReturnValue=@ReturnValue
[/code]

and the output looks like this:

[code lang=”sql”]
ReturnValue
———–
33343

(1 row(s) affected)
[/code]

All it does is use the RETURN statement to send back the value that was passed in.

This is often used to test for error conditions and stop processing if one is found.

and we can get the value back in to our C# – ADO.NET code like below

[code lang=”sql”]

public int GetCustomerMaxID()
{

string connectionString =
@"Server=.SQLEXPRESS; Initial Catalog=Northwind; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("dbo.GetCustomerMaxID"))
{
cmd.CommandType = CommandType.StoredProcedure;

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

cmd.Parameters.Add(returnValue);

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

cmd.ExecuteNonQuery();

int maxID = Int32.Parse(cmd.Parameters["@Return_Value"].Value.ToString());

conn.Close();

return maxID;
}
}

return 0;
}

[/code]

Have fun!! Happy Coding..