How to mention declared integer in where condition using dynamic sql?

Question

I tried with 1,2 and 3 quotes and with/without conversion, but I am getting

Error: Conversion failed when converting the varchar value

Can someone please help me?

with conversion

 left join xxx.xxx.PITM'+@Datemm_yyyy+' pitm 
 on nep.s_pitm_c=pitm.pitm_c
 and cast(year(s_tran_d) as nchar)='''+@yyyy+''' 
 and cast(month(s_tran_d) as nchar) ='''+@mm+'''
 
 leftjoin xxx.xxx.PITM'+@Datemm_yyyy1+' pitm1 
 on nep.s_pitm_c=pitm1.pitm_c
 and cast(year(dateadd(m,-1,s_tran_d)) as nchar) =''+ @yyyy1 +'' 
 and cast(month(s_tran_d)-1 as nchar)= '''+@mm1 +'''
 

without conversion

 inner join xxx.xx.PITM'+@Datemm_yyyy+' pitm on nep.s_pitm_c=pitm.pitm_c
 and year(s_tran_d) =''+@yyyy+'' and month(s_tran_d) =''+@mm+''
 
 inner join xxx.xx.PITM'+@Datemm_yyyy1+' pitm1 on nep.s_pitm_c=pitm1.pitm_c
 and year(dateadd(m,-1,s_tran_d)) =''+ @yyyy1 +'' and month(s_tran_d)-1= ''+@mm1 +''
 
 I also tried by keeping 1 or 2 or 3 single quotes. 
 

When I put 2 quotes I am getting

Error: Conversion failed when converting the varchar value '+@yyyy+' to data type int.

Full code:

Declare @sql nvarchar(max)
Declare @StartDate Date;
Declare @EndDate Date;
Declare @Datemm_yyyy Char(7);
Declare @StartDate1 Date;
Declare @EndDate1 Date;
Declare @Datemm_yyyy1 Char(7);
declare @yyyy int;
declare @yyyy1 int;
declare @mm int;
declare @mm1 int;
Set @StartDate='20050501'
Set @EndDate=EOMonth(@StartDate);
Set @Datemm_yyyy=Replace(right(Convert(Varchar(10),@StartDate,105),7),'-','_')
Set @StartDate1='20050401'
Set @EndDate1=EOMonth(@StartDate1);
Set @Datemm_yyyy1=Replace(right(Convert(Varchar(10),@StartDate1,105),7),'-','_')
set @yyyy=year(@StartDate)
set @mm=month(@StartDate)
set @yyyy1=year(@StartDate1)
set @mm1=month(@StartDate1)
print @StartDate
print @EndDate
print @Datemm_yyyy
print @StartDate
print @EndDate
print @Datemm_yyyy
print @yyyy
print @yyyy
print @mm
print @mm1
While @StartDate<'20181101' 
Begin
 Set @sql='
 
insert into ##loan_account_details_focis
select 
nep.s_pitm_c,
nep.s_tran_d,
pitm.LOAN_CNSN_STRT_D loancategory_start_date,
pitm1.LOAN_CNSN_STRT_D lastmonth_loan_catgory_start,
pitm.TFPR_D transfer_pricing_date
from 
xxx.DBO.RK_NEP_PROBABLE_POOL nep
left join xxx.xx.PITM'+@Datemm_yyyy+' pitm on nep.s_pitm_c=pitm.pitm_c
 and cast(year(s_tran_d) as nchar)=@yyyy
 and cast(month(s_tran_d) as nchar) =@mm
leftjoin xxx.xx.PITM'+@Datemm_yyyy1+' pitm1 on nep.s_pitm_c=pitm1.pitm_c
and cast(year(dateadd(m,-1,s_tran_d)) as nchar) =@yyyy1 and cast(month(s_tran_d)-1 as nchar)= @mm1
'
 exec (@sql)
 print @sql
 Set @StartDate=DateAdd(Month,1,@StartDate)
 Set @EndDate=EOMonth(@StartDate);
 Set @Datemm_yyyy=Replace(right(Convert(Varchar(10),@StartDate,105),7),'-','_')
 Set @StartDate1=DateAdd(Month,1,@StartDate1)
 Set @EndDate1=EOMonth(@StartDate1);
 Set @Datemm_yyyy1=Replace(right(Convert(Varchar(10),@StartDate1,105),7),'-','_')
 set @yyyy=year(@StartDate)
 set @yyyy1=year(@StartDate1)
 set @mm=month(@StartDate)
 set @mm1=month(@StartDate1)
 --print @startDate
End
;

Answer 1

Look at the output from the print, and you can see the problem that the error is reporting:

cast(year(s_tran_d) as nchar)=@yyyy

here you are explicitly casting the result from YEAR() to a character format then comparing it to @yyyy which is an integer. You do not need that case, you instead need to cast the variable as you make it part of the SQL. So instead of

cast(year(s_tran_d) as nchar)=@yyyy

you want something like

YEAR(s_tran_d) = '+@CAST(@yyyy AS NVARCHAR(10))+'

The same problem exists elsewhere in your query too.

---

As an aside, when asking further questions try to create a smaller example that illustrates your issue. See How to create a Minimal, Complete, and Verifiable Example for database-related questions for more details on that.

For instance your example could easily be cut down to:

Declare @sql nvarchar(max)
Declare @StartDate Date = '20050501';
Declare @EndDate Date = EOMonth(@StartDate);
Declare @Datemm_yyyy Char(7) = Replace(right(Convert(Varchar(10),@StartDate,105),7),'-','_');
Declare @StartDate1 Date = '20050401';
Declare @EndDate1 Date = EOMonth(@StartDate1);
Declare @Datemm_yyyy1 Char(7) = Replace(right(Convert(Varchar(10),@StartDate1,105),7),'-','_');
declare @yyyy int = year(@StartDate);
declare @mm int = month(@StartDate);;
declare @yyyy1 int = year(@StartDate1);
declare @mm1 int = month(@StartDate1);
 Set @sql='
 
insert into ##loan_account_details_focis
select 
nep.s_pitm_c,
nep.s_tran_d,
pitm.LOAN_CNSN_STRT_D loancategory_start_date,
pitm1.LOAN_CNSN_STRT_D lastmonth_loan_catgory_start,
pitm.TFPR_D transfer_pricing_date
from 
BISANDPIT_CUSTOMERREM.DBO.RK_NEP_PROBABLE_POOL nep
inner join xxx.xx.PITM'+@Datemm_yyyy+' pitm on nep.s_pitm_c=pitm.pitm_c
 and cast(year(s_tran_d) as nchar)=@yyyy
 and cast(month(s_tran_d) as nchar) =@mm
inner join xxx.xx.PITM'+@Datemm_yyyy1+' pitm1 on nep.s_pitm_c=pitm1.pitm_c
and cast(year(dateadd(m,-1,s_tran_d)) as nchar) =@yyyy1 and cast(month(s_tran_d)-1 as nchar)= @mm1
'
 print @sql
-- exec (@sql)

and that is still much larger than it could be cut down to while still illustrating the error. This will greatly increase your chances of getting useful answers to your questions.

(in other words: as much as is possible, help us to help you)

• Ramu: Perfect and thank you David. sorry, its painful if we mention code like that. Yes its working now, if I do small change YEAR(s_tran_d) = '+CAST(@yyyy AS NVARCHAR(10))+'

Answer 2

IMHO the best option to deal with dynamic SQL is:

1. Build your command
2. PRINT the resultant text.
3. Copy & paste and execute it in a new query window just to check it.

Example:

CREATE TABLE t (id int identity, foo varchar(10));
INSERT INTO t VALUES ('RED'),('GREEN'),('BLUE');
DECLARE @cmd NVARCHAR(MAX);
DECLARE @param VARCHAR(10) = 'GREEN';
SET @cmd = 'SELECT id, foo FROM t WHERE foo = ' + @param
PRINT @cmd;

Then execute the command:

SELECT id, foo FROM t WHERE foo = GREEN

SELECT id, foo FROM t WHERE foo = GREEN

Msg 207 Level 16 State 1 Line 1
Invalid column name 'GREEN'.

db<>fiddle here