SQL Topics
Parameters in Procedure
title: Parameters in Procedure
In the previous lesson, you learned how to create Stored Procedures using:
CREATE PROCEDUREThe procedures we created contained fixed SQL statements.
Example:
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT *
FROM Employees;
END;This procedure always returns all employees.
But what if we want:
Specific Employee
Specific Department
Specific Salary Rangeinstead of fixed results?
To achieve this, Stored Procedures use:
ParametersParameters allow values to be passed into a procedure dynamically during execution.
This makes procedures flexible, reusable, and suitable for real-world applications.
What are Procedure Parameters?
Procedure Parameters are variables that receive values when a procedure is executed.
Think of them as:
Inputs
Given To A ProcedureThe procedure uses these values while executing SQL statements.
Why Are Parameters Important?
Without parameters:
One Procedure
One Fixed ResultWith parameters:
One Procedure
Many Different ResultsExample:
Instead of creating:
GetEmployee1
GetEmployee2
GetEmployee3You create:
GetEmployee(EmployeeID)and pass any ID you want.
How Parameters Work
Process:
Create Procedure
↓
Define Parameters
↓
Execute Procedure
↓
Pass Values
↓
Procedure Uses ValuesTypes of Procedure Parameters
Most database systems support:
Input Parameters
Input Parameters receive values from the user.
Example:
EmployeeID = 1passed to a procedure.
The procedure uses that value.
Basic Syntax
CREATE PROCEDURE ProcedureName
(
ParameterName DataType
)
AS
BEGIN
SQL Statements
END;Creating Sample Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);Sample Data:
INSERT INTO Employees VALUES
(1,'Rahul',50000),
(2,'Priya',60000),
(3,'Amit',70000);Procedure with One Parameter
Example:
CREATE PROCEDURE GetEmployee
(
@EmployeeID INT
)
AS
BEGIN
SELECT *
FROM Employees
WHERE EmployeeID =
@EmployeeID;
END;Purpose:
Retrieve Specific EmployeeUnderstanding the Procedure
Parameter:
@EmployeeIDreceives a value.
Example:
1Procedure becomes:
SELECT *
FROM Employees
WHERE EmployeeID = 1;Executing Parameterized Procedure
EXEC GetEmployee 1;Result:
| EmployeeID | EmployeeName | Salary |
|---|---|---|
| 1 | Rahul | 50000 |
Another Example
EXEC GetEmployee 2;Result:
| EmployeeID | EmployeeName | Salary |
|---|---|---|
| 2 | Priya | 60000 |
Same procedure.
Different result.
Multiple Parameters
A procedure can accept multiple values.
Example:
CREATE PROCEDURE GetEmployeesBySalary
(
@MinSalary DECIMAL(10,2),
@MaxSalary DECIMAL(10,2)
)
AS
BEGIN
SELECT *
FROM Employees
WHERE Salary BETWEEN
@MinSalary
AND
@MaxSalary;
END;Executing Multiple Parameters
EXEC GetEmployeesBySalary
50000,
70000;Result:
Employees Between
50000 And 70000returned.
String Parameters
Example:
CREATE PROCEDURE FindEmployee
(
@EmployeeName VARCHAR(100)
)
AS
BEGIN
SELECT *
FROM Employees
WHERE EmployeeName =
@EmployeeName;
END;Execution:
EXEC FindEmployee 'Rahul';Date Parameters
Example:
CREATE PROCEDURE GetOrders
(
@OrderDate DATE
)
AS
BEGIN
SELECT *
FROM Orders
WHERE OrderDate =
@OrderDate;
END;Execution:
EXEC GetOrders
'2025-01-01';Output Parameters
Output Parameters return values from a procedure.
Think of them as:
Procedure Result VariablesOutput Parameter Syntax
CREATE PROCEDURE ProcedureName
(
@OutputValue INT OUTPUT
)
AS
BEGIN
SQL Statements
END;Output Parameter Example
CREATE PROCEDURE EmployeeCount
(
@TotalEmployees INT OUTPUT
)
AS
BEGIN
SELECT
@TotalEmployees =
COUNT(*)
FROM Employees;
END;Purpose:
Return Employee CountExecuting Output Parameter
DECLARE @Count INT;
EXEC EmployeeCount
@Count OUTPUT;
SELECT @Count;Result:
3Input-Output Parameters
Some databases support:
within the same parameter.
Example:
Value Sent
↓
Modified
↓
ReturnedReal-World Example: Banking
Procedure:
Get Account DetailsParameter:
Account NumberExample:
EXEC GetAccount
1001;Returns account information.
Real-World Example: E-Commerce
Procedure:
Get Product DetailsParameter:
Product IDExample:
EXEC GetProduct
501;Returns product information.
Real-World Example: Payroll System
Procedure:
Calculate SalaryParameters:
EmployeeID
Month
YearProcedure calculates payroll dynamically.
Real-World Example: Hospital Management
Procedure:
Get Patient InformationParameter:
PatientIDReturns patient records.
Benefits of Procedure Parameters
Reusability
One procedure handles many scenarios.
Flexibility
Different inputs produce different outputs.
Reduced Code Duplication
No need for multiple procedures.
Better Maintainability
Single procedure easier to manage.
Supports Dynamic Queries
Real-world applications depend on parameters.
Common Errors
Wrong Data Type
Example:
EXEC GetEmployee 'ABC';when EmployeeID expects INT.
Missing Parameter
Example:
EXEC GetEmployee;without required value.
Incorrect Parameter Order
Multiple parameters passed incorrectly.
Forgetting OUTPUT Keyword
Output parameter won't work properly.
Best Practices
Use Meaningful Parameter Names
Example:
@EmployeeID
@CustomerID
@OrderDateValidate Inputs
Check parameter values before processing.
Use Appropriate Data Types
Match table column types.
Document Parameters
Describe purpose clearly.
Avoid Too Many Parameters
Keep procedures manageable.
Common Interview Questions
What are Procedure Parameters?
Variables that receive values when a procedure executes.
Why are Parameters Used?
To make procedures dynamic and reusable.
What are Input Parameters?
Parameters that receive values from users.
What are Output Parameters?
Parameters that return values from procedures.
Can a Procedure Have Multiple Parameters?
Yes.
A procedure can accept multiple inputs and outputs.
Summary
Procedure Parameters allow stored procedures to accept and return values dynamically. They make procedures flexible, reusable, and suitable for real-world applications where different inputs require different results.
In this lesson, you learned:
- What Procedure Parameters are
- Input Parameters
- Output Parameters
- Multiple Parameters
- String Parameters
- Date Parameters
- Real-world examples
- Benefits and limitations
- Best practices
Mastering Procedure Parameters is essential because most enterprise stored procedures rely on dynamic inputs and outputs.
Next Step
Continue to the next lesson:
Execute Procedure →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Parameters in Procedure.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, stored, procedures
Related SQL Topics