TSQL 将多值 Reporting Services 参数传递到动态 SQL

发布于 2024-07-17 00:17:29 字数 700 浏览 8 评论 0原文

我正在使用报告服务报告中的参数构建动态 SQL 语句。报告服务以基本 CSV 格式传递 MutiValue 参数。例如，状态列表可以表示如下： AL,CA,NY,TN,VA

在 SQL 语句中，这是可以的：

WHERE customerState In (@StateList)

但是，动态变量不行：

SET @WhereClause1 = @WhereClause1 + 'AND customerState IN (' + @StateList + ') '

这是因为它转换为（无效的 SQL）：

AND customerState IN (AL,CA,NY,TN,VA)

要处理它需要这样的东西：

AND customerState IN ('AL','CA','NY','TN','VA')

是否有一些很酷的表达式可以用来将单引号插入到我的动态 SQL 中？

原文

Duplicate of: TSQL varchar string manipulation

I'm building a dynamic SQL statement out of parameters from a reporting services report. Reporting services passes MutiValue Parameters in a basic CSV format. For example a list of states may be represented as follows: AL,CA,NY,TN,VA

In a SQL statement this is OK:

WHERE customerState In (@StateList)

However, the dynamic variant isn't OK:

SET @WhereClause1 = @WhereClause1 + 'AND customerState IN (' + @StateList + ') '

This is because it translates to (invalid SQL):

AND customerState IN (AL,CA,NY,TN,VA)

To process it needs something like this:

AND customerState IN ('AL','CA','NY','TN','VA')

Is there some cool expression I can use to insert the single quotes into my dynamic SQL?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

笔芯 2024-07-24 00:17:29

由于某种原因，当与 IN 一起使用时，REPLACE 对我不起作用。我最终使用了 CHARINDEX

WHERE CHARINDEX( ',' + customerState + ',', ',' + @StateList + ',' ) > 0

REPLACE didn't work for me when used with IN for some reason. I ended up using CHARINDEX

WHERE CHARINDEX( ',' + customerState + ',', ',' + @StateList + ',' ) > 0

回复收藏 0 原文

我一向站在原地 2024-07-24 00:17:29

对于任何尝试在 where 子句中使用带有多值参数的动态 SQL 并使用它来运行 SSRS 报告的人，这就是我解决这个问题的方法...

create table #temp
(id, FName varchar(100), LName varchar(100))

declare @sqlQuery (nvarchar(max))
set @sqlQuery =
'insert into #temp
select 
id, 
FName, 
LName 
from dbo.table'
exec (@sqlQuery)

select 
FName, LName
from #temp
where #temp.id in (@id) -- @id being an SSRS parameter!

drop table #temp

当然，此查询的问题在于动态 SQL 将选择dbo.table 中的所有内容，然后从 #temp 中进行选择是过滤器启动的地方，因此如果有大量数据 - 可能不是那么好。但是......我在尝试让 REPLACE 工作或其他人发布的任何其他解决方案时感到沮丧。

For anyone attempting to use Dynamic SQL with a multi-valued parameter in the where clause AND use it to run an SSRS report, this is how I got around it...

create table #temp
(id, FName varchar(100), LName varchar(100))

declare @sqlQuery (nvarchar(max))
set @sqlQuery =
'insert into #temp
select 
id, 
FName, 
LName 
from dbo.table'
exec (@sqlQuery)

select 
FName, LName
from #temp
where #temp.id in (@id) -- @id being an SSRS parameter!

drop table #temp

Granted, the problem with this query is that the dynamic SQL will select everything from dbo.table, and then the select from #temp is where the filter kicks in, so if there's a large amount of data - it's probably not so great. But... I got frustrated trying to get REPLACE to work, or any other solutions others had posted.

回复收藏 0 原文