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:
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