SQL Server stored procedure parameters (Complete tutorial)

In this article, we will learn about the SQL Server stored procedure parameters. We will discuss how the parameters work in stored procedures with some practical examples. The following list contains the topics regarding the stored procedure parameters that we will discuss in this article.

SQL Server stored procedure parameter types
SQL Server stored procedure parameter max length
SQL Server stored procedure parameters optional
SQL Server stored procedure parameters from select
SQL Server stored procedure parameters system table
SQL Server stored procedure parameters case sensitive
SQL Server stored procedure parameters not null
SQL Server stored procedure check parameter null
SQL Server stored procedure parameters null
SQL Server stored procedure parameters default
SQL Server stored procedure parameter array
SQL Server stored procedure parameter as table
SQL Server stored procedure parameter boolean
SQL Server stored procedure order by parameter
SQL Server stored procedure allow null parameter insert update

Table of Contents

SQL Server stored procedure parameter types

We define the parameters in a stored procedure when we create a SQL Server stored procedure. In SQL Server, there are two types of stored procedure parameters:

Input Parameters
Output Parameters

Input Parameters:

Usually, we use stored procedures to perform an operation on some data. When we call the stored procedure, we pass the data values to the stored procedure through input parameters. The stored procedure takes these input parameters and uses them for completing the operation.

The general syntax for defining the input parameter is:

CREATE PROCEDURE <procedure name> <input parameter 1> <data type>
     <input parameter 2> <data type>,..... <input parameter N> <data type>
AS
<body of stored procedure>

While defining the stored procedure parmeters, we need to define the data type of the input parameters. We can define multiple input parameters separated by commas.
We can use these input parameters in the stored procedure body.

Let us see an example now:

In the code below, we have created a stored procedure with two input paramaters of ineteger type.
We will use these two input values to find their sum.

USE master
GO
CREATE PROCEDURE dbo.FindSum @num1 int, @num2 int
AS
BEGIN
	DECLARE
		@sum int
	SET @sum= @num1 +@num2
	PRINT('Sum is:'+ STR(@num1+ @num2))
END

Now we will execute the stored procedure and pass two values to the input parameters.

USE master
GO
EXEC dbo.FindSum 25, 15

SQL Server stored procedure parameter types — Stored Procedure Output

In this way, we use input parameters in SQL Server.

Read Alter Stored Procedure in SQL Server

Output Paramaters:

When we perform an operation using a stored procedure, we may want some output values to be returned by the procedure, to use those output values somewhere else. In such cases, we use the output parameters in stored procedures.

While declaring the output parameter, we need to use the OUTPUT keyword to specify that the parameter is of output type.
The general syntax for declaring an output paramater is:

CREATE PROCEDURE <procedure name> <output parameter> <data type> OUTPUT
AS
<body of stored procedure>

Let us see an example.
Consider the above created stored procedure to find the sum.
We will alter the same procedure. But this time we will return the result through an output parameter.

USE master
GO
ALTER PROCEDURE dbo.FindSum @num1 int, @num2 int, @sum int OUTPUT
AS
BEGIN
	SET @sum= @num1 +@num2
END

The @sum variable is the integer type output variable that will pass the value to the calling procedure.
While executing the procedure, we need to mention the variable into which we want to store the output value.

USE master
GO
DECLARE
	@result int
EXEC dbo.FindSum 45, 57, @result OUTPUT
PRINT(@result)

In our example, we are storing the output value in the @result variable.

stored procedure parameter types SQL Server — Executing the stored procedure

Thus, you might have understood the types of SQL Server stored procedure parameters types.

Read Try catch in SQL Server stored procedure

SQL Server stored procedure parameter max length

In this section, we will discuss the stored procedure parameters’ max length.

If you are talking about the maximum number of parameters that you can pass to a stored procedure then the maximum number is 2100. You cannot pass more than 2100 parameters.

But if you are talking about the size of the parameter that can be defined in a stored procedure, then the maximum size is 2^31-1 bytes (2 GB). That means if you are defining a parameter of any data type, the maximum data it can store is 2^31-1 bytes (2 GB).

Read Stored procedure for search functionality in SQL Server

SQL Server stored procedure parameters optional

In SQL Server stored procedures, you can also define optional parameters. For this, you have to assign a default value to the parameter you want to make optional. As a result, if no value is passed by the calling function or procedure, a default value is passed to the stored procedure. Let us see an example of this.

We have a table called Persons.

SQL Server stored procedure parameters optional — **Persons** Table

We will create a stored procedure to insert values into the table. We will pass the values as the input parameteres:

USE master
GO
CREATE PROCEDURE dbo.InsertIntoPersons @FirstName nchar(10), @LastName nchar(10),
	@Age smallint, @Gender nchar(7), @Email nchar(30), @Phone nchar(20), @Location nchar(20)
AS
BEGIN
	INSERT INTO dbo.Persons(
	[First Name], [Last Name], Age, Gender, Email, Phone, Location)
	VALUES( @FirstName, @LastName, @Age, @Gender, @Email, @Phone, @Location)
END

Suppose we do not pass any value to the @Phone parameter. In such case, we will face an error.

USE master
GO
EXEC dbo.InsertIntoPersons @FirstName= 'Thomas', @Lastname= 'Edison', @Age= 34,
	@Gender= 'Male', @Email= 'thomasthomas123@gmail.com', @Location= 'Austin'

SQL Server stored procedure optional parameters — Error When the Optional Parameter is not Defined

But we can make these parameters optional by assigning them some default values.
For assigning default values, alter the stored procedure as:

USE master
GO
ALTER PROCEDURE dbo.InsertIntoPersons @FirstName nchar(10), @LastName nchar(10),
	@Age smallint, @Gender nchar(7), @Email nchar(30)='N/A', @Phone nchar(20)= 'N/A', @Location nchar(20)
AS
BEGIN
	INSERT INTO dbo.Persons(
	[First Name], [Last Name], Age, Gender, Email, Phone, Location)
	VALUES( @FirstName, @LastName, @Age, @Gender, @Email, @Phone, @Location)
END

We have defined ‘N/A’ as default value to the @Phone parameter. This means if no value is passed, the default value will be passed to the parameter.

Now if we try to exceute the stored procedure without passing any value to the @Phone parameter, we will not get any error.

USE master
GO
EXEC dbo.InsertIntoPersons @FirstName= 'Thomas', @Lastname= 'Edison', @Age= 34,
	@Gender= 'Male', @Email= 'thomasthomas123@gmail.com', @Location= 'Austin'
SELECT * FROM dbo.Persons

stored procedure optional parameters SQL Server — Row Inserted With the Optional Parameter

Thus, you might have learned how you can create optional parameters in a SQL Server stored procedure.

Read: Find Store Procedure in SQL Server by Table Name

SQL Server stored procedure parameters from select

In this section, you will learn how to use the results of a Select statement as the input parameters in a stored procedure in SQL Server. You can use the following approach to perform this task:

Create some variables and store the results of the Select statement in those variables.
Define input parameters of the stored procedures according to the previoulsy created variables.
Pass the variables storing the results of the Select statements to the stored procedure input parameters while executing the stored procedure.

Let us understand this with an example:

Consider the following Customers table.

SQL Server stored procedure parameters from select — **Customers** Table

Firstly, we will create a store procedure that will simply display the values of the input parameters.

USE master
GO
CREATE PROCEDURE dbo.SPCustomers @ID int, @Name nchar(20)
AS
BEGIN
	PRINT('Customer Name is:'+@Name)
	PRINT('Customer ID is:'+STR(@ID))
END

Then we will create two variables to store the Customer ID and the Customer Name values.
Now we will execute the select statement and store the results into the variables.
Once the values are stored in the variables, pass these variables as the input parameters to the stored procedure.

USE master 
GO
DECLARE
@CustomerID int,
@CustomerName nchar(20)

SELECT @CustomerID= [Customer ID]
	,@CustomerName= [Customer Name]
	FROM dbo.Customers
	WHERE [Customer ID]= 1200
EXEC dbo.SPCustomers @CustomerID, @CustomerName

stored procedure parameters from select SQL Server — Executing the Stored Procedure

As you can see, the desired values are printed in the output.

Thus, you might have understood how to use the output of a Select statement as the input parameters in stored procedures.

Read SQL Server stored procedure return value

SQL Server stored procedure parameters system table

In this section, we will discuss how and where the stored procedure parameters are stored in the system tables. You will see multiple system tables which you can use to get information about the SQL Server stored procedure parameters.

You can use the following query to get information about the stored procedures and parameters:

SELECT proced.name, para.*
FROM sys.procedures proced
INNER JOIN sys.parameters para ON proced.object_id = para.object_id

SQL Server stored procedure parameters system table — Querying the System Tables

The above query will give you various information about the stored procedure parameters like to which stored procedure does it belong to, the object id, the parameter id, parameter maximum length and much more.
If you want to get information about the data type of the stored procedure parameters, you can query the types table. You can use an INNER JOIN with other sytem tables to get the desired information.

SELECT proced.name, typ.name, typ.max_length, para.*
FROM sys.procedures proced 
INNER JOIN sys.parameters para ON proced.object_id = para.object_id
INNER JOIN sys.types typ on para.system_type_id = typ.system_type_id

stored procedure parameters system table SQL Server — Querying the System Tables

The INFORMATION_SCHEMA.PARAMETERS also stores information about the parameters. If not satisfied with the above informatiojn, you can use the below queries to find more information about the parameters:

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS

SELECT * FROM INFORMATION_SCHEMA.ROUTINES

Thus, you might have seen the various ways to get information about the stored procedure parameters in the system tables.

Read SQL Server stored procedure vs function

SQL Server stored procedure parameters case sensitive

In this section, we will discuss the case sensitivity in SQL Server stored procedures. The case-sensitivity depends on the collation configuration that you have selected at the time of database creation.

Generally, when you install the SQL Server and create a database, the default collation is case insensitive. Therefore, when you create some objects or store some data in the database, all the names and data are case insensitive i.e. the data can be either in lowercase or uppercase. Also, the stored procedure parameters are case insensitive in such situations.

You can verify what collation you are using by the following command:

USE master
GO
SELECT SERVERPROPERTY ('Collation')

SQL Server stored procedure parameters case sensitive — Current Collation Settings

The current collation configuration is SQL_Latin1_General_CP1_CI_AS.
The CI in the above configuration stands for Case Insensitive.

SQL Server stored procedure parameters not null

Sometimes you might need to prevent a stored procedure parameter from taking a null value. You might think to use the NOT NULL constraint while declaring the parameter as you do when you declare a variable. But you cannot do this. You will face an error.

The right way to implement this functionality is to verify the value of the parameter. Verify if any NULL value is passed to the stored procedure as a parameter and take the desired action. To learn more about this technique, you can refer to the next section below.

Read SQL Server stored procedure insert into with examples

SQL Server stored procedure check parameter null

There are scenarios when a null value may be passed to a stored procedure parameter. But what if you do not want to accept the null values. In this section, you will learn how you can check if a stored procedure parameter contains a null value or not.

Let us see example,
We are creating a stored procedure that will retrieve data from a table.

USE master
GO
CREATE PROCEDURE dbo.SPOrderDetails @OrderID int
AS
BEGIN
	BEGIN TRY
		IF @OrderID IS NULL
			RAISERROR('A NULL Value is passed',15,1)
		ELSE
			SELECT * FROM dbo.OrderDetails WHERE [Order Number]=@OrderID
		END TRY
	BEGIN CATCH
		PRINT('Null Values are not allowed in this procedure')
	END CATCH
END

We have implemented a verification that will check if any parameter contains the NULL value or not.
Now let us execute the stored procedure with a valid input.

USE master
GO
EXEC dbo.SPOrderDetails 8001

SQL Server stored procedure check parameter null — Executing the Stored Procedure with a Valid Input

The resuls are displayed as expected.
This time try to pass a NULL value to the parameter.

USE master
GO
EXEC dbo.SPOrderDetails NULL

stored procedure check parameter null SQL Server — Executing the Stored Procedure with a NULL value

As expected, the exception is thrown when a NULL value is passed.

Thus, you might have learned how you can check if any stored procedure parameter contains a NULL value or not.

SQL Server stored procedure parameters null

In this section, we will discuss the NULL value in the stored procedure parameters. You will learn how you can assign NULL values to a stored procedure parameter. Also, how you can use the NULL value as the default value in a stored procedure parameter.

We are creating a stored procedure that will accept an input parameter and prints its value.

USE master
GO
CREATE PROCEDURE dbo.NullValue @Parameter int
AS
BEGIN
	SELECT @Parameter AS ShowValue
END

To pass a NULL value, execute the stored procedure as:

USE master
GO
EXEC dbo.NullValue NULL

SQL Server stored procedure parameters null — Passing the NULL value to the Parameter

You can also use the NULL parameter as the default parameter. In the next section below you can learn how you can use the default parameters to avoid the error if no value is passed to a stored procedure.

Just initialize the parameter with the value NULL when you create the stored procedure to assign the NULL value as the default parameter.

Read SQL Server stored procedure if else

SQL Server stored procedure parameters default

In this section, you will learn the usage and importance of the default parameters in SQL Server with the help of an example.

We use default parameters in SQL Server to ensure that if no value is provided to the parameter then a default value is treated as the supplied value. This helps to prevent the program from breaking in case if no value is supplied to the parameter. Also, if you want to give a default value to a column instead of a null value, you can use the default parameters.

For example, you have created a stored procedure and defined some input parameters. Now if you are not supplying any value to the stored procedure parameter at the time of executing it, the SQL Server database engine will throw an error stating the expected input parameter.

To avoid such conditions, you can use a default parameter where you doubt that there may be a case when no value will be passed to the parameter. We have created an example below for demonstrating the use of default parameters.

Consider the following Products table.

SQL Server stored procedure parameters default — **Products** Table

We will create a stored procedure that will insert a record in this table.

USE [master]
GO
CREATE PROCEDURE dbo.SPProducts @ID int, @Name nchar(30)
	, @Price real, @Rating int
AS
BEGIN
	INSERT INTO dbo.Products([Product ID], Name, Price, Rating)
	VALUES(@ID, @Name, @Price, @Rating)
END

Assume a situation if the product has no rating and we do not specify the value for the Rating column while executing the stored procedure. We will get an error if we do this. Let us see the error.

USE master
GO
EXEC dbo.SPProducts 1244, 'Hair Conditioner', 350

SQL Server stored procedure default parameters — Error because of no value supplied to the @Rating parameter

We can use a default parameter to avoid this error.
We will alter the stored procedure and provide a default value of 0 in a case when no value is specified for the @Rating parameter.

USE [master]
GO
ALTER PROCEDURE dbo.SPProducts @ID int, @Name nchar(30)
	, @Price real, @Rating int=0
AS
BEGIN
	INSERT INTO dbo.Products([Product ID], Name, Price, Rating)
	VALUES(@ID, @Name, @Price, @Rating)
END

If we execute the stored procedure without passing a value to the @Rating parameter, we will not get any error. Instead, the default value i.e. 0 will be used in the stored procedure.

USE master
GO
EXEC dbo.SPProducts 1244, 'Hair Conditioner', 350

stored procedure default parameters SQL Server — Default Value Inserted

You can see that the default value i.e. 0 is inserted in the table.

Thus, you might have known the use of default parameters and their importance.

SQL Server stored procedure parameter array

There is no concept of passing an array as a parameter in SQL Server. However, you can achieve this functionality with the help of table-valued parameters.

You can create a table-valued parameter and create a column for storing the values that you want to pass as an array. Later, in the stored procedure, you can access those values from the table-valued parameter as you access from a table.

For more information on table-valued parameters and how to create them, you can refer to the next section below.

Read How to view stored procedure in SQL Server

SQL Server stored procedure parameter as table

In SQL Server stored procedures, you can also pass table data to the input parameters and perform various operations on it. You will learn in this section how you can pass the table data to the stored procedure input parameters. You will get to learn better with the help of an example.

Firstly, you have to create a user-defined table data type. Then create a variable of this user-defined data type, insert some values into the variable and pass it to the stored procedure as an input parameter. For example:

Consider the following Phones table.

SQL Server stored procedure parameter as table — **Phones** Table

Firstly, we will create a user-defined table data type as the Phones table.

 USE master
GO
CREATE TYPE dbo.Phonestype AS TABLE
(
[First Name] nchar(10),
[Last Name] nchar(10),
Phone nchar(20)
)

Then we will create a stored procedure that will take the table variable as the input parameter and insert this table variable data into the Phones table.

USE master
GO
CREATE PROCEDURE dbo.InsertIntoPhones @TableParameter dbo.Phonestype READONLY
AS
BEGIN
	SET NOCOUNT ON
	INSERT INTO dbo.Phones( Phone, [First Name], [Last Name])
	SELECT Phone, [First Name], [Last name]
	FROM @TableParameter
END

Now we will declare a table valued variable insert some data into it that we want to pass to the stored procedure as the parameter.
Also, we will execute the stored procedure and pass the table valued variable as the inout parameter.

DECLARE
	@TableVariable dbo.Phonestype
INSERT INTO @TableVariable( Phone, [First Name], [Last Name])
VALUES('+1 786 984 206', 'Jonathan', 'James')
EXEC dbo.InsertIntoPhones @TableVariable

stored procedure parameter as table SQL Server — **Phones** Table Updated

As expected, the row is successfully inserted into the Phones table.

Thus, you might have learned how you can pass the table data as the input parameter to a stored procedure.

Read SQL Server select from stored procedure

SQL Server stored procedure parameter boolean

In this section, you will learn about how to pass boolean values to the stored procedures in SQL Server. In SQL Server, if you want to use boolean values, you have to use the bit data type. A bit data type variable can store 3 values:

0: Can be treated as false.
1: Can be treated as true.
NULL: No value or null value provided.

You can use this data type where you want to use some boolean variables. We will discuss in this section, how you can use a bit variable to pass a boolean value to the stored procedure. See the example below:

Assume that we have a Login table storing usernames and passwords of people.
We want to create a stored procedure that will take a user’s credentials and verify them in the table.
We want a boolean value as the return value from the stored procedure. We will write the procedure as:

USE master
GO
CREATE PROCEDURE dbo.SPLogin @User nchar(20), @Pass nchar(20)
	,@Result bit OUTPUT
AS
BEGIN
	IF EXISTS(SELECT * FROM dbo.Login
		WHERE Username= @User AND Password= @Pass)
		SET @Result= 1
	ELSE
		SET @Result= 0
END

If the credentials are already available in the database, the stored procedure will return a true value i.e. 1 else it will return a false value i.e. 0 through the output parameter.
Now let us execute the stored procedure by providing the credentials that are already stored in the database.

USE master
GO
DECLARE
	@Boolean bit
EXEC dbo.SPLogin 'Harry', 'harry123', @Boolean OUTPUT
PRINT(@Boolean)

SQL Server stored procedure parameter boolean — Returned 1 i.e. True

Now we will give the credentials that are not stored in the database.

USE master
GO
DECLARE
	@Boolean bit
EXEC dbo.SPLogin 'random', 'random', @Boolean OUTPUT
PRINT(@Boolean)

stored procedure parameter boolean SQL Server — Returned 0 i.e. False

Thus, you might have learned how you can use the bit data type as a boolean in a stored procedure parameter.

SQL Server stored procedure order by parameter

In this section, you will learn how you can decide the order of the Select query results in a stored procedure based on the input parameter.

We have created an example where user will pass an ORDER BY clause. For example, ASC, DESC, etc as the input parameter to the stored procedure.
Further, we will create a variable in which we will store the select query and concatenate the Order By clause with the query.
Then we will execute the query.

USE master
GO
CREATE PROCEDURE dbo.SPPersons @OrderBy nchar(5)
AS
BEGIN
	DECLARE
		@Query nchar(60)
	SET @Query= 'SELECT * FROM dbo.Persons ORDER BY [First Name] '+ @OrderBy
	EXECUTE(@Query)
END

Now we can execute the stored procedure:

USE master
GO
EXEC dbo.SPPersons @OrderBy= 'DESC'

SQL Server stored procedure pass parameters by name — Records Arranged in Descending Order

As you can see the records are arranged in the descending order.

SQL Server stored procedure allow null parameter insert update

You may like the following SQL server tutorials:

In this tutorial, we learned about SQL Server stored procedure parameter types.

SQL Server stored procedure parameter types
SQL Server stored procedure parameter max length
SQL Server stored procedure parameters optional
SQL Server stored procedure parameters from select
SQL Server stored procedure parameters system table
SQL Server stored procedure parameters case sensitive
SQL Server stored procedure parameters not null
SQL Server stored procedure check parameter null
SQL Server stored procedure parameters null
SQL Server stored procedure parameters default
SQL Server stored procedure parameter array
SQL Server stored procedure parameter as table
SQL Server stored procedure parameter boolean
SQL Server stored procedure order by parameter
SQL Server stored procedure allow null parameter insert update

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.