SQL Server Error : 119, Severity: 15. Must pass parameter number %d and subseq

SQL Server Error : 119 Details

SQL Server Error: 119
Severity: 15
Event Logged or not: No
Description:
Must pass parameter number %d 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’.
Severity 15 Description:
Indicates syntax errors in the Transact-SQL command.

One of the most commonly used objects in SQL Server is the stored procedure. The error message we’ll be discussing in this article is related to a stored procedure and its parameters. Let me explain the error message and how to resolve it.

EXECUTE sys.sp_one 'One', @Table_Owner = two, 'three'

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:

The reason for this error is that we passed the first parameter with the parameter name and its value (@tsql) but not the second parameter with the parameter name and its value. Instead, we simply passed the value of the second parameter to the stored procedure.

Three methods for resolution are provided below.

1.In this method, you must pass the names of all parameters as well as their values. In this method it allows you to quickly debug any errors (misplacement of parameters and their values) in the parameters. @ params line below is shown.
The script is provided below.

EXECUTE sys.sp_one 'One', 'two', 'three'

2. You should not pass any parameter names along with the value in this method. Simply enter the parameter values separated by a comma. This method is not recommended because it causes problems if you have a large number of parameters in a stored procedure and need to debug any errors in the parameters. Line without @params but variable.
The script is provided below.

EXECUTE sys.sp_one @first = 'One', @second = 'two', @three = 'three'

When executing stored procedures, the third method of passing parameters is a combination of the first and second methods, in which some parameters are passed only with values and others are passed using the “@name=value” convention. When using this method, there is a limitation. Once the “@name=value” convention is used in the list of parameters, all subsequent parameters passed should also use the “@name=value” convention rather than just passing the value, or the error above will occur.

The second method, which employs the “@name=value” parameter passing convention, is the most secure of the three. This will avoid any confusion and incidents.

SQL Server Error Code and solution summary

SQL Server Error: 119
Severity: 15
Event Logged or not: No
Description:
Must pass parameter number %d 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 parameters to any stored procedure we need to make sure that we pass the parameter name along with the values. that is we must pass the names of all parameters as well as their values if we need names.