在SQL Server Business Intelligence Development Studio中将多值参数转换为临时表 [英] Turning a multi-value parameter into a temp table in SQL Server Business Intelligence Development Studio

问题描述

我想在MS SQL Server BIDS(SSMS和Visual Studio)中创建一个报告.用户将输入电子邮件地址列表作为参数.因此，@ pEmails是'[e-mail]'，'[e-mail]'等.这些电子邮件地址可能在表中也可能不在表中.

I want to create a report in MS SQL Server BIDS (SSMS and Visual Studio). The user would enter a list of email addresses as a parameter. So @pEmails would be '[e-mail]', '[e-mail]', etc. These email addresses may or may not be in a table.

我可以简单地做到:

and Table.Email in (@pEmails)

可行，除了如果找不到电子邮件地址，我还需要返回该电子邮件地址.因此结果将类似于:

and that works, except I need to return the email address if it's NOT found as well. So the results would be something like:

|email       |found in table|  
|------------|--------------|  
|[e-mail]|  Y           |  
|[e-mail] |  N           |

我当时想我可以将输入的值列表作为@pEmails参数，并使用它们创建一个临时表，然后可以将其与之断开连接，但是我的尝试没有成功.

I was thinking I could take the list of values entered as the @pEmails parameter and create a temp table with them, which I could then left join with, but my attempts to do so have not worked out.

declare @pEmails table (EmailAddress varchar(255));
insert into @pEmails values (@ReportParameter1);

select
*
from
@pEmails

如果仅将单个值放入@ ReportParameter1，则上述方法有效，但如果其中多个则不起作用.

The above works if only a single value is put into @ReportParameter1, but not if multiples are in it.

我正在使用SQL Server2008.有关如何最好地进行操作的任何建议?

I am using SQL Server 2008. Any suggestions on how best to proceed?

推荐答案

如上所述，您需要某种split函数，以分析各种方法的性能

As has been stated, you need some kind of split function, for analysis on the performance of various methods Split strings the right way – or the next best way is an excellent read. Once you have your function, you then need to define your query parameter as a string, rather than a table:

因此您的查询实际上将变为:

So your query would actually become:

DECLARE @pEmails TABLE (EmailAddress varchar(255));

INSERT @pEmails (EmailAddress)
SELECT  Value
FROM    dbo.Split(@pEmallString);

然后转到数据集属性，而不是将多值参数@pEmails传递给数据集，而是创建一个新的@pEmailString并将值设置为表达式，该表达式应为:

Then go to your dataset properties, and instead of passing the multivalue parameter @pEmails to the dataset, instead create a new one @pEmailString, and set the value as an expression, which should be:

=Join(Parameters!pEmails.Value, ",")

这会将您的多值参数转换为单个以逗号分隔的字符串.似乎需要倒退，您需要将其转换为带分隔符的字符串，然后才可以在SQL中对其进行拆分，但是不幸的是，我不知道有更好的方法.

This turns your multivalue parameter into a single comma delimited string. It seems pretty backwards that you need to convert it to a delimited string, only to then split it in SQL, unfortunately I don't know of a better way.

这篇关于在SQL Server Business Intelligence Development Studio中将多值参数转换为临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！