Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
I encountered this error while working with some dynamic SQL in a stored procedure:
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
The solution is simple, and was a 'Doh!' moment after I figured it out. Not much help in Google when I looked, so here's for the next poor soul.
sp_ExecuteSql accepts a list of parameters, the first two of which MUST be either ntext, nchar or nvarchar. The @statement parameter is declared as ntext, so an implicit conversion is performed if you use either nchar or nvarchar.
If you are building dynamic SQL, you should declare your parameter as one of the accepted types (e.g., declare @sql nvarchar(4000)). If you are using a direct statement as your query, you need to preface it with the letter N, as seen below.
incorrect:
execute sp_executesql 'select * from pubs.dbo.employee where job_lvl = @level',
'@level tinyint',
@level = 35
correct:
execute sp_executesql N'select * from pubs.dbo.employee where job_lvl = @level',
N'@level tinyint',
@level = 35
More information is in the SQL Server Books Online, or at the following link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
I was building dynamic SQL, and had declared the SQL parameter as varchar. Doh!