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 parameters are used to pass information into a procedure from the line that executes the parameter. The parameters are given just after the name of the procedure on a command line. Commas should separate the list of parameters.

The values can be passed to stored procedures by:
  1. By supplying the parameter values exactly in the same order as given in the CREATE PROCEDURE statement.
  2. 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:
IdNameStd_CoursePhoneStd_Grade
3Harri Robins3787881
4Joe Philip3464562





(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:
IdNameStd_CoursePhoneStd_Grade
3Harri Robins3787881
4Joe Philip3464562





(2 row(s) affected)


IdNameStd_CoursePhoneStd_Grade
3Harri Robins3787881
4Joe Philip3464562





(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:
IdNameStd_CoursePhoneStd_Grade
1Joe Mathew2123452





(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:
IdNameStd_CoursePhoneStd_Grade
2Rock Feller4465653





(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