SQL Server Portal

The parameter type for ‘%.*ls’ cannot be deduced because no type would make the query valid is one of the new error messages shipped with SQL Server 2012. This error message is related to sp_describe_undeclared_parameters, a new system stored procedure shipped with SQL Server 2012 as well.

Lets discuss this in detail:
Message Number: 11507

Severity : 16

Error Message: The parameter type for ‘%.*ls’ cannot be deduced because no type would make the query valid.

Error Generation:

Given below is the script that I tried to execute but it gave me this error.

USE AdventureWorks2012
GO
sp_describe_undeclared_parameters @tsql = N'
Select * from [Production].[ProductModel]
Where ProductModelID=@ProductModelID
And Instructions =@Instruction'
GO
--OUTPUT

Msg 11507, Level 16, State 1, Line 1
The parameter type for ‘@Instruction’ cannot be deduced because no type would make the query valid.

Ooopps…… I am unable to execute it.

Resolution:

To resolve this issue you need to go to details of sp_describe_undeclared_parameters because sp_describe_undeclared_parameters does not support few data types and if those data types comes as undeclared parameters, it generates the error message.
Given below are the details of unsupported data types:

• image
• text
• ntext
• xml

Lets rewrite the script and remove the xml parameter from the script.
Given below is the script:

USE AdventureWorks2012
GO
sp_describe_undeclared_parameters @tsql = N'
Select * from [Production].[ProductModel]
Where ProductModelID=@ProductModelID '
--OUTPUT

Conclusion :
Remember, whenever you use sp_describe_undeclared_parameters, make sure you are using only supportive data type columns.

Let me know if you faced this issue and solved it in a different way.