How use out parameter in stored procedure in SQL Server?

In this SQL Server tutorial, we will learn what are Output Parameters in SQL Server Stored Procedure, How to use Output parameters in a stored procedure, and will cover the following topics.

• SQL Server stored procedure output parameter
• SQL Server stored procedure output parameter data types
• SQL Server stored procedure assign value to output parameter
• SQL Server stored procedure output parameter default value
• SQL Server stored procedure output parameter table variable
• SQL Server stored procedure output parameter returns null
• SQL Server stored procedure output parameter varchar
• SQL Server stored procedure output parameter varchar(max)
• SQL Server stored procedure output parameter cursor
• SQL Server stored procedure output parameter @@identity

Here I have used sql server 2019 and sql server management studio.

Table of Contents

• SQL Server stored procedure output parameter
• SQL Server stored procedure output parameter data types
• SQL Server stored procedure assign value to output parameter
• SQL Server stored procedure output parameter default value
• SQL Server stored procedure output parameter table variable
• SQL Server stored procedure output parameter varchar
• SQL Server stored procedure output parameter varchar(max)
• SQL Server stored procedure output parameter cursor
• SQL Server stored procedure output parameter @@identity
• SQL Server stored procedure output parameter returns null

SQL Server stored procedure output parameter

A stored procedure in SQL Server is a piece of SQL code that we can save and reuse over and over. Stored procedures logically group one or more Transact-SQL statements and store them as a named object in the database.

Now if we call the stored procedure for the first time, then SQL Server generates an execution plan and saves it in the cache. After this, SQL Server reuses the plan in successive executions of the stored procedure to execute the stored procedure quickly and reliably.

So, if we want to use a query multiple times in SQL Server. For this, we can create and save a store procedure and execute it whenever required.

Now to create a stored procedure in SQL Server, we can use the following syntax.

CREATE PROCEDURE procedure_name
AS
   sql_statement
GO;

And to execute the created stored procedure, we have to use the following syntax.

EXEC procedure_name;

We can also pass parameters to a stored procedure so that it can execute based on the value(s) of the parameter(s) passed. And the stored procedure can also return one or more output parameters.

So, in this section, we will learn how we can use a stored procedure with output parameters in SQL Server. And to create an output parameter in a stored procedure, we can use the following syntax given below.

parameter_name datatype OUTPUT

To define an output parameter in a stored procedure, we have to use the OUTPUT clause with the parameter name in the stored procedure.

Example

Now, let’s understand this with the help of an example. And for example, consider the following employee table given below.

Employee Table

Now, we will use the stored procedure output parameter to get the count of the employees whose salary is greater than a certain amount. And for this implementation, we are going to create a stored procedure as shown below.

CREATE PROCEDURE FindEmpCount (
    @salary INT,
    @emp_count INT OUTPUT
) AS
BEGIN
    SELECT * FROM Employees
    WHERE Salary > @salary;

    SELECT @emp_count = @@ROWCOUNT;
END;

• In the above example, we are creating a stored procedure with 2 parameters, out of which emp_count is an output parameter.
• In the procedure, we are using the SELECT statement with WHERE clause to filter out the employees whose salary is greater than 60,0000.
• In the end, we are using the @@ROWCOUNT to get the count of rows returned by the previous statement.
• And we are storing the count value in the output parameter.

Next, we will execute this stored procedure by using the following syntax.

DECLARE @count INT;

EXEC FindEmpCount
    @salary = 60000,
    @emp_count = @count OUTPUT;

SELECT @count AS 'Number of employees';

In the query first, we are declaring a variable that will store the value returned by the output parameter. Next, we need to use the variable in the stored procedure call. In the end, we will get the following result.

Declare output parameter in sql server stored procedure

This is how to declare output parameter in sql server stored procedure.

Read SQL Server INSERT INTO SELECT

SQL Server stored procedure output parameter data types

There are 3 important things that we need to specify while creating an output parameter in a SQL Server stored procedure.

parameter_name data_type OUT|OUTPUT

First is the parameter name, second is the data type that a parameter should hold, and in the last, we should use either the OUT or OUTPUT keyword.

Generally, any valid data type, such as integer, date, or varying character (varchar), can be used for the output parameters. But there are some exceptions in the data types that we cannot use for output parameters in a stored procedure.

So, in this section, we will discuss exception data types that we cannot use for output parameters.

• Table- It is an unique data type for storing a result set for further processing. The table data types are generally used to store a set of rows that are returned as the table-valued function result set. The table-valued data type is not supported for the output parameter of the stored procedure.
• text- It is used to store variable-length non-Unicode data with a maximum string length of 2 gigabytes. This data type is not supported for output parameters and even Microsoft has stated that this data type will be removed from the future version of SQL Server.
• ntext- This data type is used to store variable-length Unicode data with a maximum string length of 2^30 – 1 (1,073,741,823) bytes. And, it will also be removed from the future version of SQL Server.
• image- It is used to store variable-length binary data with a maximum length of 2 gigabytes, and it is also not supported as an output parameter.

SQL Server stored procedure assign value to output parameter

In Stored Procedures, the Output Parameters are used to return a value or a set of values. A Stored Procedure can have as many output parameters as it wants.

After declaring an output parameter, the next important step is to assign some value to it. Now, there can be many ways through which we can assign value to an output parameter. But two methods are simple and efficient at the same time, and we will discuss both in this section.

The first is by using the SELECT statement, and the second is by using the SET clause.

Now for demonstration, consider a simple example illustrated below.

CREATE PROCEDURE TEST
( @a datetime  OUT,
  @b datetime  OUT
)
AS
BEGIN
SELECT @a = GETDATE() --using SELECT to assign value
SET @b = GETDATE()    --using SET to assign value
END

In the above example, we have created a stored procedure with 2 output parameters, “@a” and “@b“. And both the output parameters are of DateTime data type. Next, for parameter “@a“, we are using the SELECT statement to assign the current date-time value using the GETDATE() function. And for parameter “@b“, we are using the SET clause to assign the current date-time value by using the same GETDATE() function.

Next, to execute the function, we have to declare two variables that will hold the value returned by the output parameters. And for this, we are going to execute the following code.

DECLARE @x datetime,
	@y datetime

EXEC TEST @x OUT, @y OUT

SELECT @x AS 'Using SELECT', 
       @y AS 'Using SET'

After successfully executing the above example, we will get the following result.

Execution Result

This is how to assign value to output parameter in SQL Server stored procedure.

SQL Server stored procedure output parameter default value

If a parameter has assigned a default value when declared, then the parameter is considered optional. And in a procedure call, it is not essential to supply a value for an optional argument. Generally, the default value is utilized when there is no value specified while procedure call.

We can easily define a default value for an output parameter in a SQL stored procedure. For this implementation, we can use the following syntax.

parameter_name datatype = default_value OUTPUT

Now for better understand, let’s consider the following stored procedure given below.

ALTER PROCEDURE FindEmpCount (
   @zero_count INT = 0 OUTPUT
) AS
BEGIN
    DECLARE @V INT --variable to store count of rows returned
    SELECT Name FROM Employees
    WHERE Name LIKE 'z%';
    SELECT @V = @@ROWCOUNT --saving row count in a varible

	IF @V>=1
	BEGIN
	SELECT @V AS 'Number of Employees';
	END
	ELSE
	BEGIN
	SELECT @zero_count AS 'Number of Employees';
	END
END;

• In the above example first, we are declaring an output parameter (@zero_count) in the store procedure with a default value of zero (0).
• After this, we are using the SELECT statement to find all the employee names whose name starts with z.
• Then we are using the IF/ELSE block to check the number of rows returned by the SELECT statement. And if the SELECT statement does not return any row, then the default value will be displayed instead of row count.

In our case, as there are no records whose name starts with “z”, the SELECT statement will not return any rows. So default value of the output parameter will be shown in the result.

Final Result

Read String or binary data would be truncated in SQL Server

SQL Server stored procedure output parameter table variable

Unfortunately, we cannot use the table-value data type as an OUTPUT parameter of a stored procedure. The OUTPUT parameters are used to return the value to the caller of the procedure. But the table-value data type can only be accomplished by using the READONLY keyword in the SQL Server procedure.

The READONLY indicates that the parameter cannot be updated or modified within the body of the procedure. So we can only use the table-value data type as an INPUT parameter in a stored procedure.

The detailed guidelines related to this topic are available in the official documentation provided by Microsoft.

SQL Server stored procedure output parameter varchar

A stored procedure in SQL Server can store multiple output parameters. Also, the output parameter can be of any valid Transact-SQL data type like- integer (int), character(char), date. So similarly, we can easily create an output parameter of varying character (varchar) data type in a stored procedure.

For this implementation, we can use the following syntax given below.

EXEC procedure_name;

0

Now let’s understand this implementation with the help of an example. For this, we are going to create a stored procedure demonstrated below.

EXEC procedure_name;

1

In the above example, we are creating a stored procedure that will take the employee name as an input parameter and return the gender of that employee. For this, we are declaring @Name as an input varchar parameter and @Gender as an output varchar parameter. After this, we are using the SELECT statement to get the gender of an employee using the employee name, which we will pass using the @Name input parameter.

After creating the stored procedure, it’s time to execute it by using the following query.

EXEC procedure_name;

2

In the query, we have declared a varchar variable with the name “@emp_gender“, which we will use to store the output parameter. After this, we are executing the procedure by providing the parameters. In the end, we are using the PRINT statement to print the gender of an employee.

And after executing the above code, we will get the following output.

Output

Read SQL Server DateTime vs Datetime2

SQL Server stored procedure output parameter varchar(max)

Varchar(max) is a data type in SQL Server, used to store variable-length non-Unicode, character string data. The main difference between varchar(n) and varchar(max) is the number of bytes that they can store. The varchar(n) can store 1-8000 bytes of data whereas, the varchar(max) can store a maximum of 2³¹-1 bytes.

And as varchar(max) is also a valid Transact-SQL data type, we can easily use it as an output parameter data type in a stored procedure. For this implementation, we can use the following simple syntax while creating the output parameter.

EXEC procedure_name;

3

Now let’s understand this implementation with the help of an example. For this, we are going to create a stored procedure demonstrated below.

EXEC procedure_name;

4

In the above procedure, first, we have declared an input parameter with the name “@IntCharacter“. After this, we have declared an output parameter with the name “@Name“. And then, we are using the SELECT to find the employee whose name starts with the initial character defined in the “@IntCharacter” variable.

Now after creating the procedure, we will use the following query to execute this procedure.

EXEC procedure_name;

5

In this execution query, we have declared a varchar(max) variable which we will use to store the output parameter. After this, we are exciting the stored procedure by providing the input parameter as ‘Gr’. So the procedure will try to find the employee whose name starts with “Gr”. In the end, we are using the PRINT statement to print the employee’s name.

Final Output

Read Create Foreign Key in SQL Server

SQL Server stored procedure output parameter cursor

In databases, operations are usually performed on a set of rows. For example, we use a SELECT statement to get multiple rows as an output also known as a result set. But rather than working with the complete result set at once, application logic may need to work with one row at a time. And to implement this, we can use cursor in SQL Server.

The cursor in SQL Server is Transact-SQL logic that allows us to cycle through the query result. This allows us to conduct activities in sequential order, such as updating a single row.

Now, each cursor in SQL Server follows the following life cycle.

1. Declaring Cursor– First, we use a SQL statement to declare a cursor.
2. Opening Cursor– Next, a cursor is opened in order to store data from the result set.
3. Fetching Cursor– When a cursor is opened, rows can be retrieved one at a time or in bulk to do data manipulation.
4. Closing Cursor– After data processing, the cursor should be explicitly closed.
5. Deallocating Cursor- In the end, cursor definitions should be deleted, and any system resources associated with the cursor should be released.

We can use cursor data type in a stored procedure, but it can only be used for an OUTPUT parameter. And to define an output parameter of cursor data type, we can use the following syntax given below.

EXEC procedure_name;

6

So as mentioned in the above syntax, it is important to use both VARYING and OUTPUT keywords while declaring an output parameter of cursor type.

Now for demonstration, consider the following example given below.

EXEC procedure_name;

7

In the above example, we have created a procedure with the name “NameSalaryCursor“, with one cursor data type output parameter. And we are using this cursor to fetch name and salary columns in batch from the Employee table.

EXEC procedure_name;

8

Next, to execute the procedure first, we have created a cursor variable which we will use to store the value returned by the output parameter. In the end, we are fetching the values from the cursor.

And it will return the following result.

Output

Read Identity Column in SQL Server

SQL Server stored procedure output parameter @@identity

The @@IDENTITY is a system function in SQL Server that returns the last inserted identity value of a table. And we can easily use this function in a stored procedure to find the last inserted identity value.

For a better understand, let’s consider the following stored procedure given below.

EXEC procedure_name;

9

In this stored procedure, we have declared 3 input parameters and one output parameter. After this, we are using the 3 input parameters to insert values in the Employee table. And we are @@IDENTITY function to assign the last inserted identity value to the output parameter.

parameter_name datatype OUTPUT

0

Next, to implement the execution first, we have declared an integer variable that will store the value returned by the output parameter. And then, we are using the EXEC statement to insert a record into the employee table. In the last, we, are using the Print statement to display the output returned by the OUTPUT parameter.

As the output parameter returns an integer value, we are using the CAST() function to convert the integer to varchar expression. After successful execution, we will get the following output.

Final Result

Read Saving changes is not permitted in SQL Server

SQL Server stored procedure output parameter returns null

An output parameter of a stored procedure will always return NULL when we do not assign or modify the output parameter and still try to fetch the value of it.

Let’s understand this situation with the help of the example given below.

parameter_name datatype OUTPUT

1

In the above example, we are creating a store procedure that will insert a new record in the employee table. And for this, we have declared 3 input parameters and one output parameter, and we are using the 3 input parameters to assign values to be inserted. But we didn’t use or assign any value in the output parameter.

parameter_name datatype OUTPUT

2

Now, while executing the stored procedure, if we try to fetch the value of the output parameter, it will return NULL. As we didn’t assign any value to the output parameter in the procedure.

NULL result

You may like the following SQL Server tutorials:

• SQL Server Create Temp Table
• Delete Duplicate Rows in SQL Server
• SQL Server Row_Number
• SQL Server select from stored procedure
• SQL Server stored procedure vs function
• Stored procedure in SQL Server for insert and update

So in this tutorial, we have learned what is Output Parameters in SQL Server Stored Procedure, How to use Output parameters in a stored procedure, we have also covered the below topics.

• SQL Server stored procedure output parameter
• SQL Server stored procedure output parameter data types
• SQL Server stored procedure assign value to output parameter
• SQL Server stored procedure output parameter default value
• SQL Server stored procedure output parameter table variable
• SQL Server stored procedure output parameter returns null
• SQL Server stored procedure output parameter varchar
• SQL Server stored procedure output parameter varchar(max)
• SQL Server stored procedure output parameter cursor
• SQL Server stored procedure output parameter @@identity

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.

Can we use out parameter in procedure?

Can stored procedure have output parameter?

Can we use out parameter in function in SQL Server?

Can we pass parameter to stored procedure in SQL?