Create Stored Procedure with Return Values

In our previous two articles, we discussed the basics of stored procedures and learned how to create stored procedures with output parameters. If you haven't read these two posts, I strongly encourage you to do so before continuing with this article.

• how to create stored procedure with parameters in sql server
• how to create a stored procedure in sql server with output parameters

In the article, we will discuss the below points

• Stored procedure return values 
• How to return the output of stored procedure into a variable in the SQL server
• How do we differentiate between output parameter return value, and when to use between Output parameter and Return variable.

What return values are?

When you execute stored procedures, it returns an integer status variable. Typically, a zero indicates success, and a nonzero value indicates failure. Let's understand this concept practically.

I have created a table named 'Company_Customer' in our database and also created a stored procedure that takes '@Id' as an input parameter and returns the customer details.

Create Procedure [dbo].[spGetcustomerbyId]
@Id decimal
as
Begin
Select * from [dbo].[Company_Customer] where Id=@Id
End

1. Open SQL server management studio. And then the object Explorer.
2. If you open the programmability folder you should see stored procedures folder in which I have got a stored procedure spGetcustomerbyId.

If I want to execute the stored procedure, I simply right-click on it and select 'Execute Stored Procedure'. This will open the following screen.

So let's provide the value for Id and click 'OK'. I receive the customer details back, which is the expected result.

However, take a look at this. We also have a return value, which is an integer. In this case, it is 0. Typically, zero indicates success, while a non-zero value indicates failure.

So from this, we understand that when we execute a procedure, we receive an integer variable back. If the status of that integer variable is zero, then the stored procedure executed successfully, while a non-zero value indicates failure.

Now, let's try to understand the difference between these return values and output parameters.

We will discuss the differences between output parameters and return values. If you look at the procedure that I have written here...

Create Procedure [dbo].[spgetcustomercount]
@CustomerCount int Output
as
Begin
Select @CustomerCount = COUNT(Id)
from Company_Customer
End

This procedure is called spgetcustomercount. We are creating a procedure to get the total count of customers, and in the stored procedure:

• We have an output parameter @CustomerCount.
• We initialize that output parameter with the COUNT function.

Declare @TotalCustomer int
Execute spgetcustomercount @TotalCustomer Output
Select @TotalCustomer

When we execute the above query, we should obtain the total number of customers in the table.

So what have we done?

We have created a stored procedure with an output parameter that receives the total number of customers, and we have executed that stored procedure, which will display the total number of customers.

Re-written same stored procedure using return variables

Now let’s proceed to achieve the same outcome using return values. We are creating this procedure, as shown below. This procedure utilizes the return value.

So what does this procedure do?

It returns the total count of customers. Essentially, this procedure accomplishes the same task, but it uses a return value instead of a parameter.

Let’s create the second procedure.

Create Procedure spGetTotalCustomerCountReurnvalues
as
Begin
return (Select COUNT(Id) from Company_Customer)
End

Now Let’s execute that stored procedure.

Declare @TotalCustomer int
Execute @TotalCustomer = spGetTotalCustomerCountReurnvalues
Select @TotalCustomer

Look at what we're doing. We're creating a variable of type integer.

When you execute a stored procedure using the EXECUTE keyword, it returns a value back. So, we are taking that value and initializing this variable. Finally, we are selecting it or you can print it.

If you look at the image below, I'm able to print the total number of customers in my organization. You can achieve this using both output parameters and return values.

Now let’s consider an example where the return status variable cannot be used, but an output parameter can be used.

Alright, let’s say I want to create a stored procedure that returns the name of a customer when I provide the ID. So, if I pass the ID into the stored procedure, it should return the name of the customer to me.

Using Output Parameter

Create Procedure spGetCustomerNameByIdOutput
@Id int,
@Name nvarchar(20) Output
as
Begin
Select @Name = CustomerName from Company_Customer Where Id = @Id
End

Declare @CustomerName nvarchar(20)
Execute spGetCustomerNameByIdOutput 4, @CustomerName out
Print 'Name of the Customer = ' + @CustomerName

Using Return Values

Create Procedure spGetCustomerNameByIdReturnValues
@Id int
as
Begin
Return (Select CustomerName from Company_Customer Where Id = @Id)
End

If you want to execute that, you need to create a variable of type nvarchar that will store the name of the customer returned from the procedure. So, let’s do that.

Declare @CustomerName nvarchar(20)
Execute @CustomerName = spGetCustomerNameByIdReturnValues 4
Print 'Name of the Customer = ' + @CustomerName

let’s execute this and see what happens. it will return an error.

Msg 245, Level 16, State 1, Procedure spGetCustomerNameByIdReturnValues, Line 6 [Batch Start Line 9]
Conversion failed when converting the nvarchar value ‘John’ to data type int.

If you remember, at the beginning of this post, we mentioned that whenever you execute a stored procedure, it returns an integer status variable.

Now, when we execute this, the return value of this stored procedure should always be an integer. "0" indicates success, and a non-zero value indicates failure.

But what did we do here? we are trying to return a nvarchar type.

We are able to create the stored procedure successfully. But at runtime, what happens? It tries to convert the name of the customer into an integer because the return value of a stored procedure is an integer.

You cannot convert a string word into an integer, and thus it fails, resulting in this error.

So, keep in mind that you can use return values only to return an integer and only one value.

Let’s consider a scenario where I want to return the name and gender of a customer. Can I do that with a return value? No, I cannot.

Now, let’s say I want to return the PIN code and the age of a person, both of which are integers. Can I do that with a return value? No, because I can only return one value. But can I achieve the same thing with output parameters? Absolutely yes.

You can return any number of values with output parameters and any data type.