How to execute a stored procedure with parameters in SQL Server ?

Execute SQL Server stored procedures with one or multiple input parameters.

Depending on the implementation of the T-SQL code, execute SQL Server stored procedures with input parameters by providing values or default ones will be used. First you must set the value NULL in the procedure code.

This is because we create the parameters in a mandatory or a non-mandatory way. For optional parameters, we can assign a default value in the create statement of the SQL procedure. So optional parameters are not stopping from executing the procedure and getting a potential feedback in SSMS.

Execute a SQL Server stored procedure with one parameter

First of all, create the procedure to call, or make sure the target is already in the database. If not, execute the code to create the uspGetCustomer stored procedure from this tutorial. In this stored procedure, the unique input parameter is Customer ID, and it is a mandatory one.

The goal of the procedure is to display all informations. I.e., all columns from the Clients table for the client number given as a parameter. The sample stored procedure with this script:

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

Here is a simple example on how to execute a MS SQL stored procedure with one or more parameters as input. Indeed, to pass the input information to the SQL code, execute this code and adapt this syntax to the specific business case:

EXEC dbo.uspGetCustomer
   @NoClient = 1;

Run a stored procedure with multiple parameters in T-SQL

In this second case, the Client ID and the City are used as parameters. In this more practical examples it is of course possible to use only names and explicit values instead of technical IDs.

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

So to execute a stored procedure coded in SQL Server, simply call the procedure, with the mandatory parameters separated by values, and in the proper order, or named explicitly, which is the recommended way.

EXEC dbo.uspGetCustomer
   @ClientNumber = 2,
   @City         = 'Casablanca';
 

Indeed, in the previous paragraph I mention the fac that you do not need to explicitly call the variable names, you can call it this way:

EXEC dbo.uspGetCustomer
   2,
   'Casablanca';
 

But in this case, the order must be respected. For example, try to run the code below, i.e. without respecting the order.

EXEC dbo.uspGetCustomer
   'Casablanca', 
   2;

You might encounter a SQL Server error like this one, because the type of the parameters are not the same. And if they were both integers, it can be worse, because then the result will be false.

Msg 8114, Level 16, State 1, Procedure dbo.uspGetCustomer, Line 0 [Batch Start Line 0]
Error converting data type varchar to int.

A simple syntax as long as the parameters and default values are used

To go further in the SQL Server and T-SQL learning journey, this is a short tutorial on how to return values with a stored procedure using the output option. Indeed, stored procedures, unlike SQL functions, do not always return a value. To return a value with a MS SQL procedure, use the OUTPUT option.