将逗号分隔值传递给sp_executesql

I'm trying to use sp_executesql by passing CSV parameter.

我正在尝试通过传递CSV参数来使用sp_executesql。

When I use EXEC by passing constructed string, it returns me some data

当我通过传递构造的字符串使用EXEC时，它返回一些数据

declare @Accts nvarchar(100) = 'IntYTD,TotalIncome,PayoffYTD'
declare @sql nvarchar(max)
set @sql = 'select sum(TotalBalanceMTD) from rptGL where FieldName in (''' + replace(@Accts, ',', ''',''') + ''') group by FieldName'
exec (@sql)

But when I use sp_executesql and pass the @Accts as parameter, it returns no data.

但是当我使用sp_executesql并传递@Accts作为参数时，它不返回任何数据。

set @sql = 'select sum(TotalBalanceMTD) from rptGL where FieldName in (@values) group by FieldName'
declare @v nvarchar(max) = '''' + replace(@Accts, ',', ''',''') + ''''
exec sp_executesql @sql, N'@values varchar(max)', @values = @v

I can't find whats is wrong with this method

我找不到这种方法有什么问题

1 个解决方案

#1

If you change the statement as following, it will get result. For get some objective, you also can you charindex or split.

如果您将语句更改为以下内容，则会获得结果。为了得到一些目标，你也可以使用charindex或split。

set @sql = 'EXEC(''select sum(TotalBalanceMTD) from #rptGL where    FieldName in (''+@values+'') group by FieldName'')'
declare @v nvarchar(max) = '''' + replace(@Accts, ',', ''',''') + ''''
exec sp_executesql @sql, N'@values varchar(max)', @values = @v

CHARINDEX:

CHARINDEX：

 declare @Accts nvarchar(100) = 'IntYTD,TotalIncome,PayoffYTD'
 select sum(TotalBalanceMTD) from #rptGL where charindex(','+FieldName+',',','+@Accts+',')>0 group by FieldName

#1