How to execute SQL Server stored procedures with 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 input parameters
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 uspGetClient @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;
In this second sample code, the Client ID number 2 is used. In more practical examples it is also possible to use names and explicit values instead of technical IDs.
EXEC dbo.uspGetCustomer @NoClient = 2;
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.
