Input Parameters
Input parameters allow a Stored Procedure to receive values from the user or application at runtime.
What are Input Parameters?
Input parameters are values passed to a Stored Procedure when it is executed.
They make Stored Procedures reusable because the same procedure can work with different input values.
For example, instead of creating separate procedures for different Student IDs, we can create one procedure that accepts StudentId as an input parameter.
Basic Syntax
CREATE PROCEDURE ProcedureName
@ParameterName DataType
AS
BEGIN
SELECT *
FROM TableName
WHERE ColumnName = @ParameterName;
END;
Here, @ParameterName represents the input parameter passed to the Stored Procedure.
Example: Student ID Parameter
Suppose we have a Students table with columns such as StudentId, Name, City and Age.
CREATE PROCEDURE GetStudentById
@StudentId INT
AS
BEGIN
SELECT *
FROM Students
WHERE StudentId = @StudentId;
END;
Executing a Procedure with Input Parameter
After creating the Stored Procedure, we can execute it by passing a value to the input parameter.
EXEC GetStudentById @StudentId = 101;
Here, 101 is passed as the value of the StudentId parameter.
Input Parameter with VARCHAR
Input parameters can also accept text values.
CREATE PROCEDURE GetStudentsByCity
@City VARCHAR(50)
AS
BEGIN
SELECT *
FROM Students
WHERE City = @City;
END;
Executing VARCHAR Parameter
EXEC GetStudentsByCity @City = 'Pune';
This procedure returns students whose city is Pune.
Multiple Input Parameters
A Stored Procedure can accept more than one input parameter.
CREATE PROCEDURE GetStudentsByAgeAndCity
@City VARCHAR(50),
@Age INT
AS
BEGIN
SELECT *
FROM Students
WHERE City = @City
AND Age = @Age;
END;
Executing Multiple Parameters
EXEC GetStudentsByAgeAndCity
@City = 'Pune',
@Age = 22;
Named Parameter Execution
Named parameters make the EXEC statement easier to understand.
EXEC GetStudentsByAgeAndCity
@Age = 22,
@City = 'Pune';
The parameters can be supplied by name, so their order does not need to match the procedure definition.
Default Parameter Value
We can also provide a default value for an input parameter.
CREATE PROCEDURE GetStudentsByCity
@City VARCHAR(50) = 'Pune'
AS
BEGIN
SELECT *
FROM Students
WHERE City = @City;
END;
Executing Default Parameter
If we do not provide a value, SQL Server uses the default value.
EXEC GetStudentsByCity;
In this example, the procedure uses Pune automatically.
Practical Example
The following Stored Procedure accepts StudentId and returns the details of that particular student.
CREATE PROCEDURE GetStudentDetails
@StudentId INT
AS
BEGIN
SELECT
StudentId,
Name,
City,
Age
FROM Students
WHERE StudentId = @StudentId;
END;
Execute:
EXEC GetStudentDetails @StudentId = 101;
Important Points
- Input parameters receive values from the caller.
- Parameters can have different data types such as INT, VARCHAR, DATE and DECIMAL.
- A Stored Procedure can have multiple input parameters.
- Parameters can have default values.
- Input parameters make Stored Procedures reusable.
- Use named parameters when executing procedures for better readability.
Summary
Input Parameters allow Stored Procedures to receive values during execution. They make procedures flexible and reusable. A procedure can accept one or multiple parameters, and parameters can also have default values. Input parameters are commonly used to filter data and perform operations based on values supplied by the user or application.