SQL Server Portal

In my earlier article, I wrote about one of the new system stored procedure sp_describe_undeclared_parameters shipped with SQL Server 2012. This is very handy system stored procedure whenever you need to find undeclared parameters in a SQL query.
I was trying to find undeclared parameters in a query using this system stored procedure and came across with a new error message. I did some research and fixed it.

Let me explain this error in detail :

Message Number: 11508

Severity : 16

Error Message: The undeclared parameter ‘%.*ls’ is used more than once in the batch being analyzed.

Error Generation:

In the given below example, I need to search country region name and a portion of a name in First name, Middle Name & Last Name columns of the employee view. So I wrote a query and it works perfectly. Given below is the script.

USE [AdventureWorks2012]
GO
DECLARE @Search_Name AS VARCHAR(MAX)
DECLARE @Country_Region_Name AS VARCHAR(MAX)

SET @Search_Name = 'William'
SET @Country_Region_Name='United States'

SELECT
FirstName
, MiddleName
, LastName
, CountryRegionName
FROM HumanResources.vEmployee
WHERE
( FirstName  like '%' + @Search_Name + '%'
OR
MiddleName like '%' + @Search_Name + '%'
OR
LastName   like '%' + @Search_Name + '%'
)
AND CountryRegionName = @Country_Region_Name
--OUTPUT

Once the query is executed successfully, lets pass the above query to sp_describe_undeclared_parameters to find undeclared parameters.

USE AdventureWorks2012
GO
sp_describe_undeclared_parameters @tsql =
N'SELECT * FROM HumanResources.vEmployee
WHERE
( FirstName  like ''%'' + @Search_Name + ''%''
OR
MiddleName like ''%'' + @Search_Name + ''%''
OR
LastName   like ''%'' + @Search_Name + ''%''
) AND CountryRegionName = @Country_Region_Name'
--OUTPUT

Msg 11508, Level 16, State 1, Line 5
The undeclared parameter ‘@Search_Name’ is used more than once in the batch being analyzed.

Ooopps…… I am unable to execute it.

Resolution:

If you look at the ERROR, you will easily realize that @Search_Name is used as a parameter in the query multiple times. But if you remove @Search_Name from the query, the purpose of the query would be dead. So what is the remedy? It is very simple to fix, just pass @Search_Name parameter in the sp_describe_undeclared_parameters as an input parameter, and the error message will be gone. Lets do it.

USE AdventureWorks2012
GO
sp_describe_undeclared_parameters @tsql =
N'SELECT * FROM HumanResources.vEmployee
WHERE
( FirstName  like ''%'' + @Search_Name + ''%''
OR
MiddleName like ''%'' + @Search_Name + ''%''
OR
LastName   like ''%'' + @Search_Name + ''%''
) AND CountryRegionName = @Country_Region_Name'
,@params =N'@Search_Name nvarchar(50)'
--OUTPUT

Conclusion :

Remember, whenever you come across this error message just search for that particular parameter name causing error in the query and pass it into the sp_describe_undeclared_parameters as an input parameter. There is a possibility that multiple parameters can be placed in the query in multiple times. So to fix this error just pass all the parameters mentioned in the error message and run sp_describe_undeclared_parameters again.