0

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

FLICKER
  • 6,439
  • 4
  • 45
  • 75
  • 1
    `IN` doesn't take parameters and `sp_executesql` doesn't do textual replacement. Your first query constructs the whole query dynamically. The second attempts to pass the clause as a parameter, but that won't fly. The basic problem is described [here](https://stackoverflow.com/questions/337704/), along with solutions (in this case, a TVP might do nicely). – Jeroen Mostert Aug 03 '17 at 16:58
  • [Another solution using a split function in a subquery here](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a8770fa9-6230-41d0-883f-947796db421c/use-parameterized-spexecutesql-with-in-clause?forum=transactsql) – JNevill Aug 03 '17 at 16:59
  • @JeroenMostert, I was not aware of the limitation. I try to use split function or any other workaround. Thank you! – FLICKER Aug 03 '17 at 17:03

1 Answers1

1

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
Nolan Shang
  • 2,312
  • 1
  • 14
  • 10
  • Thanks for answer, but my intention is showing usage of sp_executesql versus EXEC. Also SPLIT is only available on 2016 (I'm on 2014). Thanks for posting an answer anyway. I'll wait for others to see if I can get an answer close to my need – FLICKER Aug 03 '17 at 17:30