title: How do I pass a variable that contains a list to a dynamic SQL query?
body: <p>I have a function that converts a sting list of numbers into a table of integers:</p>
  
  <pre><code>USE [IFRS_Temp]
  GO
  /****** Object:  UserDefinedFunction [dbo].[CSVToTable]    Script Date: 01/12/2019 3:36:10 PM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
  RETURNS @TempTab TABLE
     (id int not null)
  AS
  BEGIN
      ;-- Ensure input ends with comma
      SET @InStr = REPLACE(@InStr + ',', ',,', ',')
      DECLARE @SP INT
  DECLARE @VALUE VARCHAR(1000)
  WHILE PATINDEX('%,%', @INSTR ) &lt;&gt; 0 
  BEGIN
     SELECT  @SP = PATINDEX('%,%',@INSTR)
     SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
     SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
     INSERT INTO @TempTab(id) VALUES (@VALUE)
  END
      RETURN
  END
  </code></pre>
  
  <hr>
  
  <pre><code>declare @listOfIDs varchar(1000);
  SET @listOfIDs = '5, 6, 7, 8, 9, 15, 28, 31, 49, 51, 59, 61'; 
  
  select id from  [dbo].[CSVToTable] (@listOfIDs) --this code is ok5
  </code></pre>
  
  <p>The result is correct:</p>
  
  <pre><code>6
  7
  8
  9
  15
  28
  31
  49
  51
  59
  61
  </code></pre>
  
  <p>This throws an error:</p>
  
  <pre><code>exec('select id from  [dbo].[CSVToTable] ('+@listOfIDs+')') -- error
  </code></pre>
  
  <p>result :</p>
  
  <blockquote>
    <p>Procedure or function dbo.CSVToTable has too many arguments specified.</p>
  </blockquote>
  
  <p>I need the second query because my query is dynamic.
  Thanks</p>