Create Stored Procedure with return values

In our previous two articles, We have discussed the basics of stored procedures, we have understood how to create stored procedures with output parameters. If you haven’t read these two posts, I would strongly encourage you to do so before continuing 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?

whenever you execute stored procedures. It returns an integer status variable. Usually, a zero indicates success and a nonzero indicates failure, let’s understand what we mean by this practically.

I have created table “Company_Customer” in our database and also created a stored procedure which take @Id as input parameter and return the customer detail.

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.

Now if I want to execute the stored procedure, I just try to Right-click on that and select execute the stored procedure. this screen will be open.

So let’s provide the value for Id and click ‘ok‘ .I get customer detail back, which is the expected result.

But it looks at this. We have a return value as well. And that’s an integer and it is 0, usually zero indicates success and Non zero indicates failure.

so from this, we have understood that when we execute a procedure we get an integer variable back. And if that integer variable status is zero, then the stored procedure executed successfully and nonzero indicates failure.

All right with that now, let’s try and understand the difference between these return values and output parameters.

So Now we will talk about 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 for geting the total count of customer and in store procedure

• We have an output parameter @CustomerCount.
• We are initializing that output parameter with the count function.

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

when we execute above query we should get the total number of customers on the table.

So what we have done now?

we have created a stored procedure with an output parameter which was received the total number of customer and we have executed that stored procedure which will print the total number of customers.

Re-written same stored procedure using return variables

Now let’s go ahead and do the same thing using return values.So we are creating this procedure if you look at the below. This procedure is using the return value.

So this procedure what it’s doing?

it’s returning the total count of the customer and this procedure is essentially the same but differently using a parameter,

so 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.

And then when you execute a stored procedure using this execute keyword, it returns a value back.

So we are taking the value and initializing this variable and finally what you’re doing. We are selecting it or you can print it.

If you look at the below image ,I’m able to print the total number of customers in my organization, you can do this using the Output parameter as well as using return values.

Now let’s look at an example where the return status variable can not be used but Output  parameter can be used.

Alright, so now let’s say I want you to write a stored procedure that gives the name of a customer when I give it ID. So if I pass the ID into the store procedure, it has to give the name of the customer to me back.

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 customer return 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 have spoken that whenever you execute 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. and “0” indicates success and non zero 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 run time what’s going to happen? 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 string word into an integer and it fails and that’s why we get this error.

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

Let’s have a scenario that I want to return the name and gender of a customer. Can I do that with return value? So I cannot do that.

Let’s say I want to return the PIN code and the age of a person both of them that let say they are integers.

Can I do that with return value? cannot because I can return only one value but can I do the same thing with output parameters? Absolutely yes.

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

The post Create Stored Procedure with return values appeared first on Software Development | Programming Tutorials.