SQL Server stored procedure with parameters

How to create a SQL Server stored procedure with input parameters? As a reminder, a stored procedure groups and schedules a set of SQL or T-SQL commands. How can we integrate a SELECT query on the Customers table of our SQL Server database into a stored procedure?

For example, instead of displaying all the data in the table, to display a single customer, we need to filter using the parameter passed as input.

Creating a SQL Server stored procedure with parameters

Before running the script to create the stored procedure, create the Customers table first. Then use this SQL Server script to create the Clients table.

-- 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
SQL Server query to display the contents of the CLIENTS table
SQL Server query to display the contents of the CLIENTS table

T-SQL Create Procedure code 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

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

EXEC uspGetClient @NoClient = 3;

EXECUTE uspGetClient @NoClient = 3;
Result of running the SQL procedure with a parameter
Result of running the SQL procedure with a parameter

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:

Msg 201, Level 16, State 4, Procedure uspGetClient, Line 0
Procedure or function ‘uspGetClient’ expects parameter ‘@NoClient’, which was not supplied.

Manage the default value of parameters in a SQL 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;
Delete, create, and execute a SQL Server stored procedure without using parameters
Delete, create, and execute a SQL Server stored procedure without using parameters

Create a SQL stored procedure with several parameters in variables

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.

Delete, create, and execute a SQL Server stored procedure with parameters.
Delete, create, and execute a SQL Server stored procedure with parameters.

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.

In conclusion, this article explains step by step how to create a SQL Server stored procedure with parameters.

Be the first to comment

Leave a Reply

Your email address will not be published.


*