How to use Pass comma separated string in dynamic query in SQL

Question

I have a function which will return integer values from comma delimited string , It takes two parameters (@string nvarchar(4000), @delimiter char(1)). So the problem is if I am using this function inside a dynamic query I am getting error , here is query

declare @ProductIDs varchar(11)
declare @SQL varchar(max)

set @ProductIDs='1,2,3,4'
declare @query varchar(max)
--set @query= @ProductIDs +','+@Delimiter

SELECT @SQL = 'SELECT val FROM dbo.[fnDelimitedStringToTable]('+ @ProductIDs +' , '','')'

Exec(@SQL)

I am getting error Procedure or function dbo.fnDelimitedStringToTable has too many arguments specified.

Szymon · Accepted Answer · 2013-10-13 11:17:57Z

1

When you build a dynamic SQL like that, you need to wrap your parameter in double quote ''

declare @ProductIDs varchar(11)
declare @SQL varchar(max)

set @ProductIDs='1,2,3,4'
declare @query varchar(max)
--set @query= @ProductIDs +','+@Delimiter

SELECT @SQL = 'SELECT val FROM dbo.[fnDelimitedStringToTable]('''+ @ProductIDs +''' , '','')'

Exec(@SQL)

This way the SQL statement will be:

SELECT val FROM dbo.[fnDelimitedStringToTable]('1,2,3,4' , '','')

and not:

SELECT val FROM dbo.[fnDelimitedStringToTable](1,2,3,4 , '','')

answered Oct 13, 2013 at 11:17

Szymon

43k16 gold badges99 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dmehro Over a year ago

Thanks for making correction in code, I missed '' while passing parameters . Thanks a ton , you saved my weekend :-)

Hamlet Hakobyan · Accepted Answer · 2013-10-13 11:35:12Z

0

Use sp_executesql instead. In this case you can pass arguments as parameters.

DECLARE @SQL nvarchar(max)
DECLARE @ParmDef nvarchar(1000)

DECLARE @ArgProductIDs nvarchar(100)
DECLARE @Arg2 nvarchar(100)
DECLARE @Arg3 nvarchar(100)

SET @SQL = N'SELECT val
             FROM dbo.[fnDelimitedStringToTable](@ProductIDs, @Param2, @Param3)';

SET @ParmDef = N'@ProductIDs nvarchar(100),
                 @Param2 nvarchar(100),
                 @Param3 nvarchar(100)';

SET @Arg1 = N'1,2,3,4';
SET @Arg2 = N'';
SET @Arg3 = N'';

EXEC sp_executesql @SQL, @ParmDef, 
      @ProductIDs = @ArgProductIDs, @Param2 = @Arg2, , @Param3 = @Arg3

answered Oct 13, 2013 at 11:35

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Collectives™ on Stack Overflow

How to use Pass comma separated string in dynamic query in SQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related