Stored procedure with input parameters
Table of Content:
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"