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
- Connecting to SQL Server 2008 of provider using connection string
- Need to fetch only duplicate values and ignore the unique values in SQL server
- Error trying to create a database in mssql
- SQL add a variable to a query
- SQL Transaction Error: The current transaction cannot be committed and cannot support operations that write to the log file
- Correct the correlation names in my SQL query
- Visual Studio - Open a SQL file with SQL Management Studio in an existing SSMS window?
- Where am I going wrong? Selecting results that are not in a sub-query
- SQL Server (specific) table not updating
- How to set "Unavailable" word for null values in DataGridView
- SqlGeography spatial operations slow - SQL Server 2016
- Error: A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations
- Turbogears 2 Tutorials?
- Isolation levels and select for update
- Set A Variable From A Table
- SQL show records that don't exist in my table variable
- Any Performance Advantages to Consolidating SQL Server Triggers?
- How to find valid date on tables
- SQL same space between 2 format of 1 field
- Pandas.read_sql failing with DBAPIError 07002: COUNT field incorrect or syntax error
- How to insert multiple instances based on an array
- Does query execution time differs based on different query param value with huge table?
- How make sqlcmd hold file access rights on linux?
- Find table value between range of column in sql
- How to truncate the decimal values in sql server 2000
- Generate a day date according to existing month+year+daydiff
- SQL Server : join with top record selection
- To create a column Summing the values from another column in the same view
- While loop to populate a table
- Convert a column values to column names SQL server
- Openrowset with bulk option cannot retrieve records due to an invalid type DATE
- Should I JOIN or should I UNION
- How to clear shopping cart automatically after a certain period in asp.net
- Render image in asp.net MVC
- SQL Output Parameter
- any improvements to SQL query with subquery of view?
- How can I compare two columns for similarity in SQL Server?
- Display all employees under each manager with role
- Comparing the same character in VARCHAR and NVARCHAR differs between CP1/CP1252 vs. CP850 based on DB collation
- Passing parameters to stored procedure using EF6
- How do I get the results of one JOIN and THEN feed those into a separate join in T-SQL?
- How to print out the current sql query in php with sqlsrv?
- SQL - If two rows have the same date, only show row with certain column value
- Is it possible to execute queries in 3 seconds or less against a database terabytes in size?
- SQL query to assign Inventory to upcoming shipments
- C#/SQL Switch between sql tables with combobox in a single datagridview
- how to get certain sql results
- SQL Server DELETE is slower with indexes
- T-SQL select records for the month
- How to view test results in tSQLt?
- Entity Framework group by and actual SQL not efficiently written
- Returning List<int> from Database
- mssql to mysql query conversion
- Out of memory error as inserting a 600MB files into sql server express as filestream data
- Can you parameterize sort order in a sql server prepared statement?
- SQL RIGHT JOIN Won't Count Null Data
- Is it possible to apply SELECT INTO a temporary table from another SELECT?
- .NET / Entity Framework Core / SQL Server Procedures Returning Repeated Values
- Update identity column in dynamic T-SQL
- SQL Server: How to get/save result of "statistics profile"
- Running SQL agent job from SSIS
- Dynamic Variable Name for backup table
- how to assign query result to local variable in T-SQL
- What does DBMSSOCN stand for?
- Tagging should use before trigger or after trigger
- How can i check Data sync group from SSMS?
- Insert into Select does not insert the same data order correctly
- SQL Server Performance New Table vs New Columns
- Importing XML into SQL Server but trying to make multiple entries if multiple results exist for a child element
- SQL Query - Multiple columns into one column
- Why is the Data in my Computed Columns showing up with null / 0 values
- ROW_NUMBER & PARTITION
- Duplicate key exception when updating (not inserting) a field in a DB table
- how to write linq "group by" and get count based on row in asp.net mvc
- SQL -How to recreate this logic using a sql case statement in side a where clause
- Summing Course Credits with Business Logic
- Simple SSIS package for Delta change - SSIS 2008
- XML : save data from sql server to file
- MSSQL 2000 database error while using GROUP BY clause
- sql server top query
- Count from a Count based on a condition in SQL server
- Error when restoring SQL Server database from C#
- Silent Command for LocalDB
- SQL Nested Select Statement
- How to configure SSIS merge Join transformation
- Need faster concat of columns in sql server table
- Set MS SQL login roles in different cultures
- JDBC connection pool ping error in Glassfish
- Running total for SQL column that is part of subquery
- How to get only last row after inner join?
- SQL Server full text search issue
- Retrieving the selected checkbox values from a ValidationGroup
- SQL Assistance Do I run a Query on a dabase using BETWEEN a date from 5PM to 7AM the next day?
- SQL Server incrementing a column's value depending on a trigger from another table
- Renumbering rows in SQL Server
- SSIS: Precedence Constraint not working as expected
- How to tell which driver is being used by SQL Server?
- Guide to enhance my code
- Create subdirectory in Sql Server FileTable
- Arranging Rows with Similar Column Data Together After Using ORDER BY
- VB to SQL server
- c# filtering out NULL rows from Database and Updating existig rows
- why does it keep on saying "cannot find table 0"?
- Need advice on sql server tables
- SSRS 2008 R2 - evaluating running total only on change of group
- Need a Case Sensitive Exists on an attribute to find duplicates
- Is there a way to rotate a map view in SSRS?
- SSIS How to Map Hundreds of Derived Columns
- Format only part of the query results as JSON in SQL Server
- How to browserify require('net').Socket?
- getting images out of mssql in C# using streams
- Oracle Transaction under MS SSIS
- Rank consecutive null values
- Why do my results not stay consistent?
- Updating long string through SQL Server Query Analyzer
- Merging multiple rows into one row
- How to Create Identity field based on the row
- How to execute a task when a new element is INSERTED into a table in Microsoft SQL Management Studio
- SQL Server doesn't show the server properties option
- SQL Server left outer ignores the record
- Using INSERT and/or UPDATE together from a single CTE
- Return column with varying values depending on change points
- Keyword SQL Server stored procedure
- How to make a query with group_concat in sql server
- Improving the speed of insert in SQL Server
- Adding uploaded by (user name or name) Automatically while uploading the Excel into SQL Server in C#
- How to check the previous row value
- Getting the Avg Age with multiple Parameters
- Reporting Services Security - mixture of SQL Login and Windows Authentication
- How to use a case statement in scalar valued function in SQL?
- SQL Server: Extract Table Meta-Data (description, fields and their data types)
- SQL Server 2012 clean up a set of data
- Aggregate result from query by quarter SQL
- Correct Nested Query
- Rolling 7 Day Distinct Count of Active Users
- SQL date conversion to UNIX time stamp gives multiple UNIX times for same time stamp
- High Traffic SQL Table intermittent Null Output Parameter
- what is good, explicite cursor in backend or loop in front end?
- SQL Server 2008 - adding a column to a replicated table fails
- Dynamic dates in SQL Server SQL statement
- SQL - Complex RANK
- subquery is returning multiple rows without operators
- Format SQL in SQL Server Management Studio
- Import Excel Data to Database
- Is GO Supported in MySQL?
- Transact sql character order with symbols
- How to order query result by multipart X.Y[.Z] "version" numbers?
- Add a column ID with a unique value for each row
- Pick random element by percentage weight
- SQL Insert via mappings table