Specifying a Parameter Name

Each stored procedure parameter must be defined with a unique name. Stored procedure parameter names must begin with a single @ character, as in a standard Transact-SQL variable, and must follow the rules for object identifiers. The parameter name can be used in the stored procedure to obtain and change the value of the parameter.

Values can be passed to stored procedures either by explicitly naming the parameters and assigning the appropriate value or by supplying the parameter values given in the CREATE PROCEDURE statement without naming them. For example, if the stored procedure my_proc expects three parameters named @first, @second, and @third, the values passed to the stored procedure can be assigned to the parameter names, such as:

EXECUTE my_proc @second = 2, @first = 1, @third = 3;

Or by position without naming them:

EXECUTE my_proc 1, 2, 3;

Naming the parameters when executing the stored procedure allows the parameters to be supplied in any order. If the parameters are not named, they must be supplied in the same order (left to right) as they are defined in the stored procedure. Additionally, all parameters preceding a given parameter must be supplied even if they are optional and have default values. For example, if the parameters of my_proc are all optional, my_proc could be executed by supplying values only for the first and second parameters, but not by supplying values only for the second and third parameters. This is necessary because, otherwise, Microsoft SQL Server 2005 cannot identify the parameters that are being specified.

Community Additions