Monday, October 20, 2008

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

This error occurred while executing following MSSQL code.

declare @sql varchar(8000);
declare @params varchar(4000);

set @sql= 'select @perf_max= max(avrg)
from (
select avg(coalesce(ea_score, 0)) as avrg
from employee_appraisal
where ea_ap_id= 1 and ea_year='+cast(@yahr as varchar)+' and ea_emp_id in '+ @filter+
'group by ea_emp_id) v1';
set @params= '@perf_max float output';
exec sp_executesql @sql,@params,@perf_max output

I found the error was caused of calling sp_executesql with parameter type of varchar, NOT Nvarchar. @sql is dynamic query which is maximum length of 6K chars in my case. But NVARCHAR is 4K, so it's not enough.
Can anybody provide me solution?
Thanks in advance

1 comment:

Anonymous said...

try it
DECLARE @SQL NTEXT