How to create a SQL Server stored procedure with parameters ?

T-SQL tutorial to create a SQL Server stored procedure with input parameters and T-SQL variables.

Create a SQL Server stored procedure with parameters and default values. The goal of SQL procedures is to group and schedule a set of SQL or T-SQL commands. But how to integrate a dynamic SELECT query on the Customers table of a SQL Server database into a stored procedure?

For example, instead of displaying all the data from the table, display a single customer. We need to filter the table using the parameter value passed as an input hard coded value or variable.

Create the sample table

Before running the script to create the stored procedure, create the clients table first. Indeed, use this SQL Server script to create the Clients table with a simple structure and 3 columns:

1. ClientNumber
2. Name
3. City

-- Create the clients table to store the number, the name and the city
CREATE TABLE [dbo].[CLIENTS] (
   [ClientNumber]  int IDENTITY(1,1),
   [Name]          nvarchar(20) UNIQUE,
   [City]          nvarchar(20)
)
GO

1. T-SQL code to create a procedure with one parameter

This is an example of a SQL stored procedure that uses a single parameter. This parameter is the customer number. The procedure displays the customer whose number is passed as a parameter.

CREATE PROCEDURE uspGetClient
@NoClient INT
AS
	SELECT 	*
	FROM 	[dbo].[CLIENTS]
	WHERE 	[NOCLIENT] = @NoClient
GO

2. Call the stored procedure using EXEC or EXECUTE

To call the stored procedure, execute the code as follows with EXEC or EXECUTE :

EXEC uspGetClient @NoClient = 3;

EXECUTE uspGetClient @NoClient = 3;

In the previous example, the procedure call passes the value of the parameter. Similarly, execute the SQL Server procedure without passing the @NoClient parameter value:

EXEC uspGetClient;

Then the following SQL error message is displayed because the client number parameter is mandatory and is not assigned a default value:

Procedure or function ‘uspGetClient’ expects parameter ‘@NoClient’, which was not supplied.

3. Manage parameters default values in stored procedure

To go further and manage the default values, it is simple, in most cases, it is advisable to pass a value to the parameters. In practice, this is not always possible or even useful. To foresee the case where a call to the procedure is made without parameters, use the NULL value. Indeed NULL allows to initialize a parameter value in the procedure and to avoid errors.

After that, execute this second version of the stored procedure without parameters. This time, no errors and the query return no rows because all the clients in the table have a client number.

-- Test if the procedure exists and delete it
IF EXISTS (
   SELECT name 
   FROM sysobjects 
   WHERE name = 'uspGetClient' AND type = 'P'
)
  DROP PROCEDURE uspGetClient
GO

-- Create the stored procedure with a default value
CREATE PROCEDURE uspGetClient 
  @NoClient int = NULL
AS
  SELECT   *
  FROM   [dbo].[CLIENTS]
  WHERE   [NOCLIENT] = @NoClient
GO

-- Execute the stored procedure without passing any parameters
EXEC uspGetClient;

4. SQL Server stored procedure with multiple parameters

This is the same principle as with a single parameter, and each parameter must be listed, initialized, and called independently. Thus, enumerate and separate each parameter and its data type with a comma, as in this example of SQL code:

-- Test if the procedure exists and delete it
IF EXISTS (
   SELECT name 
   FROM 	sysobjects 
   WHERE 	name = 'uspGetClient' AND type = 'P'
)
  DROP PROCEDURE uspGetClient
GO

-- Create the same procedure with two parameters and default values
CREATE PROCEDURE uspGetClient 
  @ClientNumber   	int = NULL, 
  @City   		nvarchar(20) = NULL
AS
  SELECT   *
  FROM   	[dbo].[CLIENTS]
  WHERE  	[ClientNumber]  = @ClientNumber
    OR   	[City]      = @City
GO

-- Call the procedure with different parameter combinations
EXEC uspGetClient @ClientNumber = 1, @City = 'Lyon';

EXEC uspGetClient @ClientNumber = 1;

EXEC uspGetClient @City = 'Paris';

Indeed, one can call each of the parameters alone, or both simultaneously. It is possible to pass no parameters because the procedure initializes the default values with the value NULL.

SQL procedures with parameters allow flexible code

Finally, calling the uspGetClient stored procedure without parameters is possible. In this case, it returns all the data contained in the Clients table because the logical OR operator is used in the WHERE clause. This scripts simply displays data from a customers table, however stored procedures can also be used to manipulate objects, like creating and rebuilding indexes.

To continue learning, this other tutorial explains step by step how to alter a SQL Server stored procedure.

Alter SQL Server stored procedure