Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

When we pass a parameter value using the name(called Named parameter), should be little focus on that.

/*Creating a stored procedure*/
Create Proc Usp_Sample
(@Para1 Int,
@Para2 Int,
@Para3 Int
)As
Begin
Set Nocount on
Select @Para1 + @Para2 + @Para3
End
Go


/*#1. Executing the procedure*/
Exec Usp_Sample @Para1=1, 2, 3
Go


It'll throw an error...
Msg 119, Level 15, State 1, Line 1
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.


/*#2. Executing the procedure*/
Exec Usp_Sample 1, @Para2=2, 3
Go


It'll also throw an error...
Msg 119, Level 15, State 1, Line 1
Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.


Reason is...
- Whenever the parameter value is passed using Named Parameter(ie: @Name=Value), rest of the all parameters next to the named parameter also should be Named parameter.

i:e
1. If a stored procedure have 3 parameters(IN).
2. If the First parametere used with parameter name(named parameter) then, all the remaining (Second, Third) parameters also should be named parameter.

Exec Usp_Sample @Para1=1, @Para2=2, @Para3=3
Go


3. If the Second parametere used with parameter name(named parameter) then, the Third parameters also should be named parameter.But, the First parameter no need to be a Named parameter.

Exec Usp_Sample 1, @Para2=2, @Para3=3
Go


4. If the Third parametere used with parameter name(named parameter) then, the First, Second parameters no need to be a Named parameter.

Exec Usp_Sample 1, 2, @Para3=3
Go

2 comments: