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.
The post Create Stored Procedure with return values appeared first on Software Development | Programming Tutorials.
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
- SQL server agent Jobs step
- SQL merge rows with same ID to one
- Getting SCOPE_IDENTITY() after insert with select
- Why SQL Server Tuning Advisor proposes to add PRIMARY KEY to included columns of index?
- Does T-SQL offer a simple, not over-engineered way to reduce code duplication of subqueries? (No temp tables, etc.)
- Is rollback needed if query completed with errors?
- Entity Framework database first with MySQL and MS SQL
- SQL Server issues when getting records starting from a date onwards
- How do I use a wild card in the middle of an sql server like query
- Updating database using pypyodbc
- Multiple Rows to single column using SQL Server
- Java - Count Difference in minutes
- Azure App Service can't access Azure SQL DB, but local machine works. Allow-All-IP rule already in place.
- Can adding basic new SQL Server index create more problems?
- When I convert a pivot table to formulas I get #GETTING_DATA instead of values
- SQL Server 2012 - Running Total With Backlog & Carry Forward
- Does a normal SQL Server Index include Primary Key value?
- SQL database permissions required to use impersonation from SSRS connection
- How can I search for strings with characters not on the keyboard (non-english)?
- Can't have the same table names in different entity framework models?
- SQL Server get two days difference and days count from date range
- System.Data.SqlClient.SqlException: 'Incorrect syntax near '@p0'.'
- How to run SQL that has declare statements via SqlCommand?
- How to change dbo ownership to another user login in SQL Server 2008?
- would it be faster to parse XML from url or to save the informations in a database
- How to compare two multiple values columns in SQLSERVER
- SQL : Join variable table and view
- How can we test the stored procedure from the database by using the moq objects in C# Unit Testing?
- How Can I generate sequence No in SQL Query?
- Can I install SQL Server on a USB drive?
- SQL Server CE to SQL Server
- Sql server Trigger deleting few rows at once
- SQL : How to sum multiple bit columns in a row
- SQL query to fast data generation
- In SQL Server can I switch JSON output on and off via a parameter?
- SQL Foreign Key Relation
- Sharing the Users table between an IdentityDbContext, and a main application DbContext
- Why I can't create a table with this dynamic SQL?
- Select non distinct rows from two columns
- How to get only number from string in SQL Server?
- Why does parameterizing a LIKE predicate result in a higher cost?
- Table variable in SQL Server
- Replacing Value of Empty Node in SQL XML
- How do I group by min value in one field of table, keeping all the values from that same row?
- Determine first year of minimum consecutive year range and count of consecutive years
- Sql column adding another column
- SSIS SqlServer 2016 DTS ComponentMetaDataCollection always empty
- How can I generate database tables from C# in order to version control the database?
- SQL Server - ISNULL not working on Update Query
- How to use cases inside of where clause?
- Find time difference between start status and end status with pause status
- Average without calculate zero
- Simple dapper select query with 20 rows(most of columns are nvarchar(max)) taking too long- 15 seconds and more
- Using C# to create a "monthly timetable/schedule calender" in a SQL table
- Force NULL to be a string in SELECT NULL FROM Table
- SSIS Convert yyyy-mm-dd hh:mm to ISO8601
- Send a XML to a sp for insertion
- Get substring between second and fourth slash
- Convert groups of multiple key-value rows to XML
- Is there a way I can turn DELETE CASCADE OFF and then ON again during a transaction?
- How to rebuild a record from a change log
- Improving the speed of insert in SQL Server
- Entity & SQL - Error converting data type nvarchar to datetime
- How can I prevent SQL injection with Node.JS?
- Is there DDL Auditing in SQL Server 2000
- OLEDB 12.0 is not installed on local machine (ACE Driver)
- SQL Server string operations
- How can I create a new table in SQL where some rows are exact matches and some are not?
- BCP import exclude identify or some column
- SQL Inner Join On Null Values
- Parameter type cannot be determined for at least one variant parameter
- Copy entire SQL table to another and truncate original table
- How to print VARCHAR(MAX) using Print Statement?
- Getting TransactionInDoubtException when used single Transaction scope
- Deadlock on communication buffer: SQL Server 2008 R2 running stored procedures for data warehouse
- Google Style Search Suggestions with Levenshtein Edit Distance
- performance of union versus union all
- Incorrect syntax near @indexName. Dapper
- How to fix the error procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'?
- Weekending date return issue
- How can I group rows into columns with multiple values?
- TSQL - Select Count(*) ..into Varchar variable
- Why adding another LOOKUP transformation slows down performance significantly SSIS
- How do I migrate table schemas from one DB to another without damaging the data in the destination DB?
- SQL divide by zero error - ISNULL not working?
- How to add an IBM DB2 server to SQL Server's Linked Server
- Mssql/Perl DBI performance woes
- Required parameter issue dtsx power shell script
- Calculation in Sql Server
- Multipe DataSet Values in one Row
- How Can I Skip a row(an iteration) in MSSQL Cursor based on some condition?
- SQL Server Convert Varchar to Datetime
- Update Table to include calculated percentage
- Creating stored procedure in another database
- SQL 2005 Find Out Who Created a View
- How to prevent inserting "." "/" and "\" into SQL database as empty string
- Sql Server group by , count query
- date comparison in sql server
- Select query to return all rows where a column contains a domain name
- Identify distinct codes present in one column but not present in another column
- Join three tables on two columns with blanks where they don't match
- How to find out information regarding xp_passAgentInfo
- How to get last 3 years data by dropdownlist in asp.net c#?
- Why would 'alter table switch partition' fail silently?
- Strange Sql Server 2005 behavior
- SQL Server 2012 LocalDB: how to retrieve path to Binn?
- T-SQL trying to determine the largest string from a set of concatenated strings in a database
- In SQL Server, how to have select return empty string and not null when null
- Conversion failed when converting the varchar value to data type int using in clause in SQL Server
- Can a select statement include nested result-sets?
- Update the amounts in a table by the amounts from another table when the first table is column-based and the second is row-based
- TSQL: Split one column into two based on value
- while loop inside a trigger to loop through all the columns of table in sql
- Update SQL Server Database Schema with software update
- SQL Server - putting a block of code into a view/temp table
- How find the occurrences of same value in different columns using a query
- Sum of amounts of a table without union all
- Using correlated subquery in SQL Server update statement gives unexpected result
- SQL server 2008 - how to get column name and value together?
- SQL Server variable return datatype (function)
- SQL Server - Predicate and Seek Predicate on the same column
- Authorization based on assigned Permission function
- How to aggregate data for lagging months only
- Symmetric Encryption in SQL Server 2008
- How to determine if JSON data-content is identical? (Computing JSON Hash?)
- Stored Procedure doesn't like utf-16
- BACKUP failed to complete the command BACKUP DATABASE
- SQL Select Where date in (Jan and March) but not in Feb
- SQL Server Agent Job - Exists then Drop?
- use sum over partition by to calculate cumulative value
- How to query/ Insert into - a Azure SQL Server from a local instance
- Filter SQL Data to get latest for each alternate ID using MSSQL
- Find first non-null values for multiple columns
- There is already an object named ' ' in Database
- Running data from parent table with condition on child table - SQL Server
- Create a table with SQL from Query result in SSMS
- Add default value for time(7) field
- How to find a specific Foreign Key of a table through T-SQL?
- Error Calling Stored Procedure from C#/ASP.NET
- SQL language differences between MSSQL 2000 and 2005
- Retrieving the only value from XML string
- How do install pdo_sqlsrv driver in elastic beanstalk to connect SQL server db instance
- Why can't I use my server name (SQL Server)?
- Perform ranking depend on category
- Convert Time value into Integer in sql server
- SSRS Expression to Switch LastName, FirstName to FirstName LastName in field
- how to display amount 123.45 as 12345
- SQL for each column in Deleted and Inserted
- Alter GENERATED ALWAYS colum into a GENERATED BY DEFAULT identity column - Sql Server
- SQL Server : get count and sum of columns