How to Execute a Procedure with Parameters in SQL Server ?

Tutorial with T-SQL code examples to execute a SQL Server stored procedure with one or multiple input parameters.

Depending on the T-SQL code, execute a procedure with parameters in SQL Server by providing mandatory 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.

Table of Contents

1. 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 below, it is similar to the used 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 the information. 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;

2. 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';

3. Order matters when running stored procedures in T-SQL

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.

*To execute a stored procedure with parameters in SQL Server without error :order must be respected*

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.