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)

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