CODEWITHSUNDEEP
sql-serversql-server-2014dynamic-sqlsp-executesql
FLICKER
FLICKER

Reputation: 6683

Passing comma separated values to sp_executesql

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

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

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. 

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

Upvotes: 0

Views: 578

Answers (1)

Nolan Shang
Nolan Shang

Reputation: 2328

If you change the statement as following, it will get result. For get some objective, you also can you charindex or 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:

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

Upvotes: 1

Related Questions