How to use Pass comma separated string in dynamic

问题:

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.

回答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 , '','')

回答2:

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