如何在選擇查詢中傳遞參數（在 with 子句中）而不是 SQl Server SSIS 包中的 where 條件？

我想@date1在我的 SSIS OLE DB 源中傳遞一個參數 ()，所以我創建了一個變數並嘗試使用“ ?”傳遞參數，但它顯示“語法錯誤、權限違規或其他非特定錯誤”

我試過這樣：

select dateAdd(second, 1, @date1=?) StartTime, --Selecting calls from the next second of last processed time. 
convert(datetime, convert(char(19), dateAdd(minute, -1, CURRENT_TIMESTAMP), 120)) EndTime --TRIM to seconds.

但我知道我可以在 where 條件下傳遞參數，但我想在 select 中傳遞它。

精確查詢：

select dateAdd(second, 1, @date1) StartTime, --Selecting calls from the next second of last processed time. 
convert(datetime, convert(char(19), dateAdd(minute, -1, CURRENT_TIMESTAMP), 120)) EndTime --TRIM to seconds.

完整查詢：

ALTER PROCEDURE [dbo].[GET_CAll_LEVEL_DETAILS] ( @date1 DateTime )
As
With BACK_LOG_PICK(StartTime, ENDTIME) as
(
select dateAdd(second, 1, @date1) StartTime, --Selecting calls from the next second of last processed time. 
convert(datetime, convert(char(19), dateAdd(minute, -1, CURRENT_TIMESTAMP), 120)) EndTime --TRIM to seconds.
--15 mins is considered as max call time. So calls before 15 mins are backloged and selected.

--select '18-mar-2014 18:52:00' StartTime,
--'18-mar-2014 18:54:00' EndTime
),
TCD(RouterCallKeyDay,RouterCallKey,CallStartTime,CallEndTime)
As
(
select RouterCallKeyDay,RouterCallKey
,Min(DateTime) as CallStartTime, Max(DateTime) as CallEndTime from Termination_Call_Detail
where DigitsDialed in('30013900', '30013901')
group by RouterCallKeyDay,RouterCallKey
having Min(DateTime)>=(Select StartTime from BACK_LOG_PICK) 
and Min(DateTime) < (Select ENDTIME from BACK_LOG_PICK)
--Any Call started between our interested time is selected, even if the call is not ended in our time interval.
),
TCDRecords (AgentSkillTargetID,SkillGroupSkillTargetID,ServiceSkillTargetID,PeripheralID,RouteID,
 RouterCallKeyDay,RouterCallKey,DateTime,PeripheralCallType,DigitsDialed,PeripheralCallKey,
 CallDisposition,NetworkTime,Duration,RingTime,DelayTime,TimeToAband,HoldTime,TalkTime,
 WorkTime,LocalQTime,CallSegmentTime,ConferenceTime,NetworkTargetID,TrunkGroupID,DNIS,
 InstrumentPortNumber,AgentPeripheralNumber,ICRCallKey,ICRCallKeyParent,ICRCallKeyChild,
 ANI,AnsweredWithinServiceLevel,Priority,Trunk,CallDispositionFlag,RouterCallKeySequenceNumber,
 CED,CallTypeID,BadCallTag,ApplicationTaskDisposition,ApplicationData,NetQTime,CallTypeReportingDateTime,
      NetworkSkillGroupQTime,EnterpriseQueueTime) as
(
select AgentSkillTargetID,SkillGroupSkillTargetID,ServiceSkillTargetID,PeripheralID,RouteID,
 b.RouterCallKeyDay,b.RouterCallKey,DateTime,PeripheralCallType,DigitsDialed,PeripheralCallKey,
 CallDisposition,NetworkTime,Duration,RingTime,DelayTime,TimeToAband,HoldTime,TalkTime,
 WorkTime,LocalQTime,CallSegmentTime,ConferenceTime,NetworkTargetID,TrunkGroupID,DNIS,
 InstrumentPortNumber,AgentPeripheralNumber,ICRCallKey,ICRCallKeyParent,ICRCallKeyChild,
 ANI,AnsweredWithinServiceLevel,Priority,Trunk,CallDispositionFlag,RouterCallKeySequenceNumber,
 CED,CallTypeID,BadCallTag,ApplicationTaskDisposition,ApplicationData,NetQTime,CallTypeReportingDateTime,
 NetworkSkillGroupQTime,EnterpriseQueueTime from Termination_Call_Detail a , TCD b 
 where a.RouterCallKey = b.RouterCallKey  and a.RouterCallKeyDay = b.RouterCallKeyDay 
 --and Min(DateTime)>=(Select StartTime from BACK_LOG_PICK) 
--and Max(DateTime) < (Select ENDTIME from BACK_LOG_PICK) 
),
CallDisposition(RouterCallKey, RouterCallKeyDay, Hangup_Flag) as
(
select RouterCallKey, RouterCallKeyDay, 
(
case 
when max(CallDisposition) = 52 then 'AD' 
else 
   case 
   when max(CallDisposition) = 13 then 'CD' 
   else 'SD' 
   end 
end) as Hangup_Flag 
from TCDRecords group by RouterCallKey, RouterCallKeyDay
),
callType1prepare(RouterCallKeyDay,RouterCallKey,Duration, LocalQTime)
As
(
select distinct RouterCallKeyDay,RouterCallKey, MAX(Duration), SUM(LocalQTime) from TCDRecords 
WHERE PeripheralCallType =1 
group by RouterCallKeyDay,RouterCallKey
),
CallType1PrepareDistinct(RowNumber,RouterCallKeyDay,RouterCallKey,DateTime, DigitsDialed, DNIS, 

ANI,CallDisposition,NetworkTime,Duration,RingTime,DelayTime,TimeToAband,HoldTime,
 WorkTime,LocalQTime,CallSegmentTime,ConferenceTime,NetworkSkillGroupQTime,EnterpriseQueueTime) as
(
select Row_Number() Over(Partition by b.RouterCallKeyDay,b.RouterCallKey Order By b.RouterCallKeyDay,b.RouterCallKey 

desc) As RowNumber,
       b.RouterCallKeyDay,b.RouterCallKey, DateTime, DigitsDialed, DNIS, ANI,CallDisposition,NetworkTime, 

b.Duration,
           RingTime,DelayTime,TimeToAband,
           HoldTime,WorkTime,b.LocalQTime,
           CallSegmentTime,ConferenceTime as ConferenceTime,
           NetworkSkillGroupQTime,EnterpriseQueueTime FROM TCDRecords a, callType1prepare b
 WHERE a.RouterCallKeyDay = b.RouterCallKeyDay
 and a.RouterCallKey = b.RouterCallKey
 and a.Duration = b.Duration 
),
CallType1(RouterCallKeyDay,RouterCallKey,DateTime, DigitsDialed, DNIS, 

ANI,CallDisposition,NetworkTime,Duration,RingTime,DelayTime,TimeToAband,HoldTime,
 WorkTime,LocalQTime,CallSegmentTime,ConferenceTime,NetworkSkillGroupQTime,EnterpriseQueueTime)
AS
(

           SELECT RouterCallKeyDay,RouterCallKey,DateTime, DigitsDialed, DNIS, ANI,CallDisposition,NetworkTime,
           Duration,
           RingTime,DelayTime,TimeToAband,
           HoldTime,WorkTime,LocalQTime,
           CallSegmentTime,ConferenceTime as ConferenceTime,
           NetworkSkillGroupQTime,EnterpriseQueueTime FROM CallType1PrepareDistinct WHERE RowNumber = 1
),
CallType2
(RouterCallKeyDay,RouterCallKey,CallDisposition,AgentSkillTargetID,SkillGroupSkillTargetID,
AgentPeripheralNumber,HoldTime,TalkTime,WorkTime,
DateTime, DigitsDialed, DNIS, ANI)
As
(
          SELECT 

RouterCallKeyDay,RouterCallKey,CallDisposition,AgentSkillTargetID,SkillGroupSkillTargetID,AgentPeripheralNumber,HoldTime

,TalkTime,WorkTime,
          DateTime, DigitsDialed, DNIS, ANI
from TCDRecords  WHERE PeripheralCallType =2
)
select 
p1.RouterCallKey as RouterCallkey,
p1.RouterCallKeyDay as RouterCallKeyDay, 
p1.DateTime ,
AgentPeripheralNumber,
AgentSkillTargetID,
SkillGroupSkillTargetID,
p1.DigitsDialed as DNIS,
p1.ANI,
p2.TalkTime,
p2.HoldTime,
p2.WorkTime,
Duration,
p2.DNIS as Extension,
p1.LocalQTime as LocalQTime,
CD.Hangup_Flag
from CallType1 p1 
left outer join CallType2 p2 
on p1.RouterCallKey = p2.RouterCallKey 
and p1.RouterCallKeyDay = p2.RouterCallKeyDay
left outer join CallDisposition CD
on p1.RouterCallKey = CD.RouterCallKey 
and p1.RouterCallKeyDay = CD.RouterCallKeyDay;

無法將列名綁定到變數，但您可以使用動態 SQL 獲得類似的結果。

DECLARE @datetxt varchar(10) = '2015-01-01'
DECLARE @sqltxt nvarchar(1000); 

SET @sqltxt = N'SELECT dateAdd(second, 1, ' + @datetxt + ') AS StartTime FROM Termination_Call_Detail'
Exec (@sqltxt)

如果我對您的理解正確，那麼您要執行的操作應該很簡單。

我不完全確定你是如何設置@date1的，但為了讓它工作，你需要將它設置為一個 SSIS 變數，對於這個例子，我假設它是一個日期。然後，您需要將查詢創建為變數，然後將其設置為這樣的表達式。

然後你設置你的表達式並用你的變數替換你的問號，你輸入的類型轉換成這樣的字元串。

"select dateAdd(second, 1,@date1= "+ (DT_STR, 50, 1252) @[User::Date1] + " ) StartTime, convert(datetime, convert(char(19), dateAdd(minute, -1, CURRENT_TIMESTAMP), 120)) EndTime"

然後在您的 OLEDB 任務中，只需從變數中選擇查詢即可。

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