如何使用動態 sql 在 where 條件下提及聲明的整數？

我嘗試使用 1,2 和 3 引號以及有/無轉換，但我得到了

錯誤：轉換 varchar 值時轉換失敗

有人可以幫幫我嗎？

有轉換

           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 +'''
   

沒有轉換

       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. 
       

當我輸入 2 個引號時，我得到了

錯誤：將 varchar 值“+@yyyy+”轉換為數據類型 int 時轉換失敗。

完整程式碼：

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
;

查看 print 的輸出，可以看到報錯的問題：

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

在這裡，您將結果顯式轉換YEAR()為字元格式，然後將其與@yyyy整數進行比較。您不需要這種情況，而是需要將變數轉換為 SQL 的一部分。所以而不是

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

你想要類似的東西

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

您的查詢中的其他地方也存在相同的問題。

---

順便說一句，在提出更多問題時，請嘗試創建一個較小的範例來說明您的問題。有關更多詳細資訊，請參閱如何為與數據庫相關的問題創建最小、完整和可驗證的範例。

例如，您的範例可以很容易地簡化為：

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)

這仍然比在說明錯誤的同時可以減少的要大得多。這將大大增加您獲得問題有用答案的機會。

（換句話說：盡可能地幫助我們幫助你）

引用自：https://dba.stackexchange.com/questions/274286