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
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

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.

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.

- 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'

- 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

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.

- 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

- 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

- 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

- 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')

- 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

- The resuls are displayed as expected.
- This time try to pass a NULL value to the parameter.
USE master
GO
EXEC dbo.SPOrderDetails NULL

- 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

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.

- 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

- 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

- 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.

- 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

- 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)

- 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)

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'

- 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:
- SQL Server move database files
- Create Foreign Key in SQL Server
- Types of Backup in SQL Server
- Identity Column in SQL Server
- Delete Duplicate Rows in SQL Server
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
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.