How to Create a Stored Procedure with Input Parameters in SQL

Creating a stored procedure with input parameters: This SP, accepts parameters. Parameters and variables have an @ prefix in their name.

Example: Use this below Database

Code:

USE TestDataBase

Code: Create a table to understand stored procedure

CREATE TABLE tbl_customer
(
customerID INT PRIMARY KEY IDENTITY(100000000,1),
customerSSNId INT,
customerName VARCHAR(100),
customerAge int,
customerAddressLine1 VARCHAR(100),
customerAddressLine2 VARCHAR(100),
customerCityID VARCHAR(50),
customerStateID VARCHAR(50) 
)

Code: Insert a record inside the table

INSERT INTO tbl_customer VALUES (12345678, 'Rumman Ansari', 23, 'Kolkata', 'Rajarhat', '12', '29')

Code: See the inserted record

Select * from tbl_customer

Example 2: Stored Procedure With One Parameter

Code: Create another stored procesure which will take parameter while execution

/* Select All Customer with where clause
Stored Procedure Example - 2 | Stored Procedure With One Parameter
*/

CREATE PROCEDURE SelectAllCustomers1 @Address1 nvarchar(30)
AS
SELECT * FROM tbl_customer WHERE customerAddressLine1 = @Address1
GO;

Code: Execute the above stored procedure

EXEC SelectAllCustomers1 @Address1 = "Kolkata"

Example 3: Stored Procedure With Multiple Parameters

Code: Create a Store Procedure which will take multiple parameter

Setting up multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below.

/* Select All Customer with where clause
Stored Procedure Example - 3 | Stored Procedure With Multiple Parameters
*/

CREATE PROCEDURE SelectAllCustomers3 @Address1 nvarchar(30), @Name nvarchar(20)
AS
SELECT * FROM tbl_customer WHERE customerAddressLine1 =@Address1 AND customerName = @Name
GO

Code: Execute the above store procedure

EXEC SelectAllCustomers3 @Address1 = "Kolkata", @Name = "Rumman Ansari"