SQL Server Portal

Stored procedure is one of the frequently used objects in the SQL Server. The error message we are about to discuss in this article is linked with stored procedure and its parameters. Let me explain the error message and its solution.

Message Number: 119

Severity : 15

Error Message: 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’.

Error Generation:
Let me execute a stored procedure and pass its parameter values.

USE AdventureWorks2012
GO
sp_describe_first_result_set @tsql =N'
SELECT object_id,name,type_desc
FROM sys.tables
WHERE object_id=@objectid'
,N'@objectid int'
--OUTPUT

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

Ooopps…… I am unable to execute a simple stored procedure.

Resolution:
The reason behind this error is, we passed first parameter with the parameter name and its value (@tsql) but we did not pass second parameter with parameter name & its value. Instead, we just passed the second parameter value in the stored procedure. So what is the solution ?
Given below are two methods.

Method 1 :
In this method, you must pass all parameter name(s) along with the value(s). I generally recommend this method because it helps you debug any error (misplacement of parameters & its values) in the parameters quickly.
Given below is the script.

USE AdventureWorks2012
GO
sp_describe_first_result_set @tsql =N'
SELECT object_id,name,type_desc
FROM sys.tables
WHERE object_id=@objectid'
, @params =N'@objectid int'
--OUTPUT

Method 2 :
In this method you should not pass any parameter name along with the value. Just pass the parameter values separated by comma. I do not recommend this method because it puts you in trouble if you have a lot of parameters in a stored procedure and you need to debug any error in the parameters.
Given below is the script.

USE AdventureWorks2012
GO
sp_describe_first_result_set N'
SELECT object_id,name,type_desc
FROM sys.tables
WHERE object_id=@objectid'
, N'@objectid int'
--OUTPUT

Conclusion :
Remember, whenever you pass parameter(s) to any stored procedure, make sure that you pass the parameter name along with the values.