Skip to main content
9 of 14
deleted 67 characters in body
user avatar
user avatar

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

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

            inner 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+'''
        
            inner join 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 
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
'
    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
;
Ramu
  • 13
  • 2