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