T-SQL » Stored Procedures » Using Parameters
Parameters can be passed to the stored procedures. This makes the procedure dynamic.
The following points are to be noted:
- One or more number of parameters can be passed in a procedure.
- The parameter name should proceed with an @ symbol.
- The parameter names will be local to the procedure in which they are defined.
The values can be passed to stored procedures by:
- By supplying the parameter values exactly in the same order as given in the CREATE PROCEDURE statement.
- By explicitly naming the parameters and assigning the appropriate value.
Examples
Code:
CREATE PROCEDURE spSelectStudent (@Course INTEGER, @Grade INTEGER)
AS
SELECT * FROM Students
WHERE Std_Course=@Course AND Std_Grade <= @Grade
GO
EXEC spSelectStudent 3, 2;
Output:
Id Name Std_Course Phone Std_Grade 3 Harri Robins 3 78788 1 4 Joe Philip 3 46456 2
(2 row(s) affected)
Explanation:
In the above example, the procedure is defined with two parameters. It should be noted that while executing the procedure the parameters should be passed in the same order of that in the CREATE statement. In this case, the first argument corresponds to Std_Course and second argument to Std_Grade.
Language(s): MS SQL Server
Code:
EXEC spSelectStudent @Course=3, @Grade=2;
EXEC spSelectStudent @Grade=2, @Course=3;
Output:
Id Name Std_Course Phone Std_Grade 3 Harri Robins 3 78788 1 4 Joe Philip 3 46456 2
(2 row(s) affected)
Id Name Std_Course Phone Std_Grade 3 Harri Robins 3 78788 1 4 Joe Philip 3 46456 2
(2 row(s) affected)
Explanation:
Here since we are explicitly naming the parameters and assigning the appropriate value, the stored procedure allows the parameters to be supplied in any order.
Language(s): MS SQL Server
Code:
CREATE PROCEDURE spSelectStudent1 (@Course INTEGER=2, @Grade INTEGER=3)
AS
SELECT * FROM Students
WHERE Std_Course=@Course AND Std_Grade <= @Grade
GO
EXEC spSelectStudent1;
Output:
Id Name Std_Course Phone Std_Grade 1 Joe Mathew 2 12345 2
(1 row(s) affected)
Explanation:
The stored procedures can be created with optional parameters with default values, so that if no values are assigned to the parameters then the default value will be taken as the value of the parameter.
In the above example, the procedure is executed without any parameter. So it takes the default parameters, which are @Course as 2, @Grade as 3.
Language(s): MS SQL Server
Code:
EXEC spSelectStudent1 @Course=4, @Grade=4;
Output:
Id Name Std_Course Phone Std_Grade 2 Rock Feller 4 46565 3
(1 row(s) affected)
Explanation:
In the above example, the procedure takes the specified parameters of @Course as 4, @Grade as 4.
Language(s): MS SQL Server
