Stored procedure parameters allow you to pass values into and out of a stored procedure, making it flexible and reusable.

Parameters are variables that allow you to:

  • Pass input values into a stored procedure
  • Receive output values from a stored procedure
  • Make stored procedures dynamic and reusable

1.Types of Parameters

Type Description
Input Parameter Sends values into procedure
Output Parameter Returns values from procedure
Optional Parameter Has default value
Table-Valued Parameter Passes table data

2.Input Parameters (Most Common)

Syntax

CREATE PROCEDURE procedure_name
@ParameterName DataType
AS
BEGIN
SQL statements
END;

Example: Input Parameter

CREATE PROCEDURE GetEmployeeById
@EmployeeID INT
AS
BEGIN
SELECT *
FROM Employees
WHERE EmployeeID = @EmployeeID;
END;

Execute

EXEC GetEmployeeById 3;

3.Multiple Input Parameters

CREATE PROCEDURE GetEmployeeByDeptSalary
@Department VARCHAR(50),
@MinSalary INT
AS
BEGIN
SELECT *
FROM Employees
WHERE Department = @Department
AND Salary >= @MinSalary;
END;

4.Output Parameters

Output parameters are used to return values from a stored procedure.

Example: OUTPUT Parameter

CREATE PROCEDURE GetTotalEmployees
@TotalCount INT OUTPUT
AS
BEGIN
SELECT @TotalCount = COUNT(*)
FROM Employees;
END;

Execute

DECLARE @Count INT;
EXEC GetTotalEmployees @Count OUTPUT;
SELECT @Count AS TotalEmployees;

5.Input + Output Parameters Together

CREATE PROCEDURE GetDepartmentEmployeeCount
@Department VARCHAR(50),
@Total INT OUTPUT
AS
BEGIN
SELECT @Total = COUNT(*)
FROM Employees
WHERE Department = @Department;
END;

6.Optional Parameters (Default Values)

CREATE PROCEDURE GetEmployees
@Department VARCHAR(50) = NULL
AS
BEGIN
SELECT *
FROM Employees
WHERE (@Department IS NULL OR Department = @Department);
END;

Execute

EXEC GetEmployees;         -- All employees
EXEC GetEmployees 'IT';   -- IT employees only

7.Table-Valued Parameters (TVP)

Used to pass multiple rows to a procedure.

Step 1: Create Table Type

CREATE TYPE EmployeeType AS TABLE
(
EmployeeID INT,
Name VARCHAR(50)
);

Step 2: Use in Stored Procedure

CREATE PROCEDURE InsertEmployees
@Employees EmployeeType READONLY
AS
BEGIN
INSERT INTO Employees (EmployeeID, Name)
SELECT EmployeeID, Name
FROM @Employees;
END;

Step 3: Execute

DECLARE @Emp EmployeeType;

INSERT INTO @Emp VALUES (1, 'John'), (2, 'Jane');

EXEC InsertEmployees @Emp;

Categorized in:

Stored Procedures,