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

问题描述

重复: TSQL varchar字符串操作

我正在根据报告服务报告中的参数构建动态SQL语句.报告服务以基本CSV格式传递MutiValue参数.例如，状态列表可以表示为:AL，CA，NY，TN，VA

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

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

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 + ') '

这是因为它转换为(无效的SQL):

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')

我可以使用一些很酷的表达式将单引号插入动态SQL吗?

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

推荐答案

这解决了中间问题:

SET @StateList = REPLACE(@StateList，'，'，'''，''')

SET @StateList = REPLACE(@StateList, ',', ''',''')

然后引用边缘:

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

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

这篇关于TSQL将多值Reporting Services参数传递到动态SQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！