使用多个Varchar作为参数

Question

我正在尝试创建一个查询，它将允许我在SSRS中使用多个VarChars作为参数。

代码如下：

Declare @CallCodes as VarChar(10)
Set @CallCodes = ('MORC30' , 'Morc60')

;
With Data
AS
    (SELECT 
        VC.[CallCode]
          ,VC.[HospCode]
          ,VC.[HospMastID]
          ,VC.[ClientID]
          ,Row_Number () Over (Partition By HospMastID, VC.ClientID Order By HospMastID, VC.ClientID, GLCode) as Txn
    FROM 
    [RptSys].[dbo].[CSC_VuesionImport_DevDetl] as VC
    Inner Join
    [AVimark_OLTP].[dbo].[Client] as C
    on
    VC.HospMastID = C.HospitalMasterID
    and
    VC.ClientID = C.ClientID
    Inner Join
    [Avimark_OLTP].[dbo].[Patient] as P
    on
    VC.HospMastID = P.HospitalMasterID
    and
    VC.PatientID = P.PatientID
    Inner Join
    [Avimark_OLTP].[dbo].[Treatment] as T
    on
    VC.HospMastID = T.HospitalMasterID
    and
    VC.MastReminder = T.Code
    Where
    VC.CallCode in @CallCodes)

当我尝试运行它时，这会给出一个错误。最后的输出是允许最终用户从SSRS报告的下拉列表中进行选择，我已经尝试了where语句的in或like的所有变体。

任何建议都将不胜感激。

Answer 1

你为什么不把参数放在一个标有#的临时表中？

CREATE TABLE #tmp(
Callcodes varchar(10))

INSERT INTO #tmp
VALUES
('MORC30'),
('Morc60')


With Data
AS
(SELECT 
VC.[CallCode]
,VC.[HospCode]
,VC.[HospMastID]
,VC.[ClientID]
,Row_Number () Over (Partition By HospMastID, VC.ClientID Order By HospMastID, VC.ClientID, GLCode) as Txn
FROM 
[RptSys].[dbo].[CSC_VuesionImport_DevDetl] as VC
Inner Join
[AVimark_OLTP].[dbo].[Client] as C
on
VC.HospMastID = C.HospitalMasterID
and
VC.ClientID = C.ClientID
Inner Join
[Avimark_OLTP].[dbo].[Patient] as P
on
VC.HospMastID = P.HospitalMasterID
and
VC.PatientID = P.PatientID
Inner Join
[Avimark_OLTP].[dbo].[Treatment] as T
on
VC.HospMastID = T.HospitalMasterID
and
VC.MastReminder = T.Code
Where
VC.CallCode in (SELECT * FROM #tmp))

我希望这就是你要找的，如果不是，请告诉我。或者像评论中提到的那样给出一个错误消息

祝您有愉快的一天&来自瑞士的问候

艾蒂安