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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s