procedure expects parameter statement of type ntext nchar nvarchar sql server

If you have such error in dynamic SQL query,

Please use NVARCHAR in declation intead of VARCHAR like below example



ALTER PROCEDURE [dbo].[SPC_GetClientFinancialDetail]  
@FullRef varchar(36)  
   
AS
declare @SourceName as varchar(10)
declare @strSourceName as varchar(50)
declare @SQLQuery1 as Nvarchar(2000)
declare @SQLQuery2 as Nvarchar(2000)
Begin

Select @SourceName= ISNULL(PDC_CRITERIA,'M') FROM WARRANT_DEFAULTS
IF(@SourceName<>'')
Begin

IF(@SourceName='M')
Begin
             set @strSourceName='VWCURRENT_WARRPAYMENTS_MONTHBASIS'
End
else
Begin
            set @strSourceName='VWCURRENT_WARRPAYMENTS_DAYBASIS'
End
 End
  else
Begin
    set @strSourceName='VWCURRENT_WARRPAYMENTS_MONTHBASIS'
End



   
Set @SQLQuery1='SELECT  isnull(sum(PAYMENT_AMT),0) as Tot    
FROM VWWARRANT_PAYMENTS    
WHERE WARRANT_ID = '''+@FullRef+''' AND DIRECT_PAYMENT =''Y'' and payment_dishonoured=''N'' and payment_qc_done=''Y''  
union  SELECT  isnull(sum(PAYMENT_AMT),0) as Tot  
FROM  '+ @strSourceName+'  
WHERE WARRANT_ID = '''+@FullRef+''' AND DIRECT_PAYMENT =''N''  and payment_dishonoured=''N'' and payment_qc_done=''Y'''
Print @SQLQuery1
EXECUTE sp_executeSQL    @SQLQuery1

   

SELECT AMOUNT_REMITTED as  PAYMENT_AMT, convert(varchar(22),payment_remitted_on,103) as REMITDATE    
FROM AUDIT_WARRANT_PAYMENTS  WHERE cast(WARRANT_ID as varbinary) = cast(@FullRef as varbinary)  
AND ACTION_PERFORMED='REMIT' AND ACTION_SOURCE='REMIT_PAYMENT'    
ORDER BY CONVERT(DATETIME, payment_remitted_on,103) ASC    
   
Set @SQLQuery2='SELECT ''Creditor'' as WhoTo,  PAYMENT_AMT,  
PAYMENT_DATE , payment_month , payment_year , payment_no  
FROM VWWARRANT_PAYMENTS    
WHERE WARRANT_ID = '''+@FullRef+''' AND DIRECT_PAYMENT =''Y'' and payment_dishonoured=''N'' and payment_qc_done=''Y''    
union all SELECT ''Sherforce'' as WhoTo , PAYMENT_AMT,  
PAYMENT_DATE , payment_month , payment_year , payment_no  
FROM '+ @strSourceName+'  
WHERE WARRANT_ID = '''+@FullRef+''' AND DIRECT_PAYMENT =''N'' and payment_dishonoured=''N'' and payment_qc_done=''Y''    
ORDER BY  payment_year ,payment_month , payment_no'
Print @SQLQuery2
 EXECUTE sp_executeSQL    @SQLQuery2


End