Create a SQL Server stored procedure with parameters

How to create a Sql Server stored procedure with input parameters ? As a reminder, a stored procedure schedules and groups a set of T-SQL queries. How can we integrate a simple SELECT query on the Customers table of our database into a stored procedure? For example, instead of displaying all the data of the table, display only one specific customer, filtered thanks to the parameter passed in input.

A parameter is a filter on a table for example. The procedure parameters are vey useful because they make the code dynamic.

How to create a Sql Server stored procedure with input parameters ?

Before executing the creation script of the stored procedure, create the Customers table first. Use the SQL Server script to create the Customers table from this article.

CREATE PROCEDURE dbo.uspGetCustomer 
  @CustomerID int
AS
  SELECT	*
  FROM		dbo.customers
  WHERE		[CustomerID] = @CustomerID
GO

EXEC uspGetCustomer @CustomerID = 3;

First step is to create the procedure and the input parameter, ie @CustomerID, to filter the data.

The second step simply calls the procedure with the value 3. So the the third customer is displayed.

If the stored procedure do not have parameters, then the code is static and the instructions will be the same every time.

Be the first to comment

Leave a Reply

Your email address will not be published.


*