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
- Open SQL server management studio. And then the object Explorer.
- 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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?