SQL Server Error Msg 119 – Must pass parameter number %d and subsequent parameters as ‘@name = value’.

In this blog post, let’s learn about the error message “119 – 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’.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.

SQL Server Error Message

119 – 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’.

Reason for the Error

The SQL Server error message 119 – “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'” is raised when a stored procedure is called with parameters that are not correctly formatted or passed in the wrong order.

In SQL Server, parameters can be passed to stored procedures in two ways: using the position of the parameter, or by explicitly specifying the parameter name with the corresponding value.

For example, the following stored procedure expects two input parameters to be passed: @FirstName and @LastName. These can be passed in either of the two ways:

CREATE PROCEDURE usp_InsertEmployee
    @FirstName nvarchar(50),
    @LastName nvarchar(50)
AS
BEGIN
  /* This is Test */
  PRINT N'Test';
END;

Method 1 – using the position of the parameter:

EXEC usp_InsertEmployee 'John', 'Doe'

Method 2 – explicitly specifying the parameter name with the corresponding value:

EXEC usp_InsertEmployee @FirstName = 'John', @LastName = 'Doe'

The error message 119 is raised if you use both methods together, or if you pass parameters using the position of the parameter after specifying a parameter by name.

Here is an example of how the error can occur:

EXEC usp_InsertEmployee @FirstName = 'Doe', 'John'

In this example, the value for the first parameter is passed using the position of the parameter, while the value for the second parameter is passed by explicitly specifying the parameter name with the corresponding value.

Solution

To fix this error, you need to ensure that all parameters are passed using the same method: either by position or by name.

Here is an example of how to correctly call the stored procedure:

EXEC usp_InsertEmployee @FirstName = 'John', @LastName = 'Doe'