sql-server - 将SSRS中的多个值参数传递给存储过程

Question

我正在尝试将多值字符串参数传递给 SQL Server 2012 存储过程中的表类型参数。我将此代码粘贴到 SSRS 的数据集中:

DECLARE @mylist clinic_list_tbltype
INSERT @mylist(n) VALUES (@pm_ChooseClinics)
EXEC sp_Skillset_Summary_With_Callbacks_Report @mylist, @pm_ChooseInterval, @pm_StartDateTime, @pm_EndDateTime

clinic_list_tbltype 是我使用一个名为“n”的 varchar(50) 字段创建的表类型。我可以从 SSMS 调用这个存储过程。像这样(并且返回得非常快):

DECLARE @mylist clinic_list_tbltype
INSERT @mylist(n) VALUES ('clinicA'), ('clinicB')
EXEC sp_Skillset_Summary_With_Callbacks_Report  @mylist, 'Daily', '6/3/2014', '6/9/2014'

我只能在 SSRS 中运行一个诊所(但速度非常慢)，但如果我尝试多个诊所，则会出现错误，指出

there are fewer columns in the INSERT statement than values specified in the Values clause

。即使为一个 clnic 运行它也可以工作，但与在 SSMS 中运行查询相比，它需要非常非常长的时间。比如 2 分钟 vs. 1 秒。一定是因为我传递的是 ('clinicA', 'clinicB') 而不是 ('clinicA'), ('clinicB')。

怎么办？

Answer 1

是的，我首先需要给你一些背景知识。

当您允许 SSRS 参数选择多个值时，多个值的选择将创建一个以逗号分隔的值字符串作为一个字符串

  'value1,value2,value3'

要使用 IN 运算符检查字符串中的值，我们需要用逗号连接字符串，如下所示 ....

  'value1','value2','value3'

您的过程

现在，在您的过程中，当您显式插入值时，它会将多个值插入到您的表中。

  INSERT INTO Table_Value_Param
  VALUES ('value1'),       --<-- 1st value/Row
         ('value2'),       --<-- 2nd Value/Row
         ('value3')        --<-- 3rd Value/Row

这会返回预期的结果，就像在程序中执行类似

的语句一样

SELECT * 
FROM Table_Name
WHERE ColumnName IN (SELECT ColumnName 
                     FROM Table_Value_Param)

另一方面，当您尝试使用 SSRS 报告参数插入表时，表会插入类似

的值

  INSERT INTO Table_Value_Param
  VALUES ('value1,value2,value3')   --<-- One Row/Value containing all the values comma separated

解决方案

在这种情况下创建TVP并没有真正的帮助，我所做的是在我的程序中使用dbo.Split()函数。

你可以在网上找到很多 split 函数的定义，一些很酷的定义可以看这里 Split Function equivalent in tsql?

创建此拆分函数后，只需在过程定义中使用此函数，您甚至不需要表值参数。

类似这样的事情...

  SELECT * 
  FROM Table_Name 
  WHERE ColumnName IN ( 
                       SELECT Value
                       FROM dbo.Split(@Report_Param, ',')
                       )