SQL Server Error Messages - Msg 119¶

Error Message¶

Server: 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'.

Causes¶

This error is encountered when calling stored procedures that contain multiple parameters and when calling the stored procedures, the parameters are passed to the stored procedure in a combination of ‘@name = value’ and just plain ‘value’.

To better illustrate how the error is encountered, we will use the sys.sp_tables system stored procedure. The sys.sp_tables system stored procedure returns a list of objects that can be queried in the current environment, or any object that can appear in a FROM clause, except synonym objects.

The sys.sp_tables system stored procedure can accept 5 parameters, namely, @table_name, which is the table used to return catalog information, @table_owner, which is the table owner of the table used to return catalog information, @table_qualifier, which is the name of the table qualifier or the database name, @table_type, which is a list of values, separated by commas, which can either be ‘table’, ‘systemtable’ or ‘view’, and lastly @fUsePattern, which determines whether the underscore (_), percent (%) and bracket ([ or ]) characters are interpreted as wildcard characters.

Given this stored procedure, the error can easily be reproduced using the following:

EXECUTE sys.sp_tables 'Order%', @Table_Owner = 'dbo', 'Northwind'

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'.

As can be seen from the EXECUTE statement, the first parameter passed was for the @table_name parameter, followed by the @table_owner parameter which uses the “@name=value” convention. The third parameter passed was for the @table_qualifier parameter but the convention used in passing the parameter is just “value” instead of “@name = value”, thus generating the error.

Here’s another example of how to reproduce the error:

EXECUTE sys.sp_tables @table_name = 'Order%', 'dbo', 'Northwind'

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'.

Solution / Workaround¶

There are three ways of passing parameters when executing stored procedures. The first way is by specifying the values in the same order as the parameters without including the parameter name. To illustrate, using the sys.sp_tables system stored procedure, the following statement will list all user tables in the Northwind database where the table name starts with the string “Order”.

EXECUTE sys.sp_tables 'Order%', 'dbo', 'Northwind', '''TABLE''', 1

One disadvantage of using this method is that if the order of the parameters in the stored procedure are rearranged for whatever reason or if a new parameter is added somewhere in the middle of the list of parameters instead of at the end, any calls to the stored procedure will now fail or may produce an incorrect result.

The second way of passing parameters when executing stored procedures is by using the “@name = value” convention, where the parameter name is included followed by the parameter value. To illustrate, using the same scenario earlier, the following statement will list all user tables in the Northwind database where the table name starts with the string “Order”:

EXECUTE sys.sp_tables @table_name = 'Order%', @table_owner = 'dbo', 
                      @table_qualifier = 'SQL2008', @table_type = '''TABLE''', @fUsePattern = 1

The advantage of using this method when calling stored procedures is that even if the order of the parameters in the stored procedure is rearranged or if a new parameter is added in any position or location within the stored procedure declaration, as long as a default value is assigned to new parameters, any call to the stored procedure will still work and will produce the same result.

The third way of passing parameters when executing stored procedures is using a combination of the first and second method, that is some parameters are passed just with values and others are passed using the “@name=value” convention. There is a restriction when using this method. Once the “@name=value” convention is used in the list of parameters, all succeeding parameters passed should now use the “@name=value” convention instead of just passing the value, otherwise the error above will be encountered.

Of all these three methods, the safest one to use is the second method which is by using the “@name=value” parameter passing convention. This will avoid any confusion and will avoid encountering the error above.