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.

SQL5 lines
CREATE PROCEDURE dbo.GetCustomerMaxID ()
AS
DECLARE @MAXID INT
SELECT MAX(ID) FROM CUSTOMER
Return  @MAXID

and we can call the proceduce like this

SQL3 lines
DECLARE @ReturnValue INT
EXEC @ReturnValue = GetCustomerMaxID
SELECT ReturnValue=@ReturnValue

and the output looks like this:

SQL4 lines
ReturnValue
-----------
33343
(1 row(s) affected)

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

Note The 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.

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

SQL22 lines
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;
}

Have fun!! Happy Coding..


Discover more from C4: Container, Code, Cloud & Context

Subscribe to get the latest posts sent to your email.

Written by

Nithin Mohan TK

Hello Namaste! 👋🏻 I'm an accomplished Enterprise Solution Architect & Engineering Leader with 20+ years of progressive experience architecting, building, and scaling transformative enterprise solutions. ⚜️ Solution Architect / AI & Cloud 📌 Galway, Ireland

1 comment

  1. […] This post was mentioned on Twitter by Nithin Mohan T K. Nithin Mohan T K said: Stored Procedures and Return Values: In a recent interview i was been asked whether stored procedures can have “re… http://bit.ly/cYiYIO […]

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.