SQL Server 2005: dynamically adding parameters to a stored procedure

I worked for a company that had to do this. It is much easier to just pass an nvarchar that is really a list that is comma delimited and then parse it when you get into the stored proc and insert the values into a temp table. The other option would be to have an xml parameter in your proc. That should also work. This is all for SQL 2005. 2008 does give you the table variable and that would be your best option.

I would try to stay away from dynamically changing your stored proc because I think that would be hard to maintain. At any given time if you try to look at the proc it could be different. Also, what happens when 2 people are trying to use your site and hit that proc at the same moment? One person's session will be modifying the procedure and the others will try to do it. This could cause a lock on the stored proc or it could cause other issues. Regardless it would be pretty inefficient.

Here is another option - though I think Anton's answer is better. You can pass in a csv string as a single parameter. Use a user-defined function to convert the csv string into a table of values, which you can join in your query. There are several csv parsing functions listed on SO and other places (though, sorry, I can't come up with a link right now).

edit: here is another option. Pass in the same csv string, then generate the sql query as a string in the procedure, and execute the string. Use the csv in an 'in' clause :

where i.ccy in (1,2,3,4)

I would not try to change the stored procedure, but (since you are on SQL Server 2005 and don't have table variable parameters) just pass in a comma separated list of values and let the procedure split them apart. You can change your C# loop to just build a CSV string and once you create a SQL split procedure, use it like:

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach to split a string in TSQL

For this method to work, you need to do this one time table setup:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this split function:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO 

You can now easily split a CSV string into a table and join on it:

select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')

OUTPUT:

ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)

Your can pass in a CSV string into a procedure and process only rows for the given IDs:

SELECT
    y.*
    FROM YourTable y
        INNER JOIN dbo.FN_ListToTable(',',@GivenCSV) s ON y.ID=s.ListValue

I use this function to split CSV text into a table of numbers, it has great performance due to various optimizations (like returning a table with a primary key which greatly influence the query optimizer to produce good query plans ever for extremely large data sets).

Also it's not limited to 4000 characters, so you can pass in very large strings.

CREATE Function [dbo].[TextSplitToInt](@list  text,
                               @delim char(1) = N',')
   RETURNS @T TABLE (ID_T int primary key)

   BEGIN
      DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL)
      DECLARE @slice nvarchar(4000),
              @textpos int,
              @maxlen int,
              @stoppos int

      SELECT @textpos = 1, @maxlen = 4000 - 2
      WHILE datalength(@list) / 2 - (@textpos - 1) >= @maxlen
      BEGIN
         SELECT @slice = substring(@list, @textpos, @maxlen)
         SELECT @stoppos = @maxlen - charindex(@delim, reverse(@slice))
         INSERT @slices (slice) VALUES (@delim + left(@slice, @stoppos) + @delim)
         SELECT @textpos = @textpos - 1 + @stoppos + 2   -- On the other side of the comma.
      END
      INSERT @slices (slice)
          VALUES (@delim + substring(@list, @textpos, @maxlen) + @delim)

      INSERT @T (ID_T)
         SELECT distinct Cast(str as int)
         FROM   (SELECT str = ltrim(rtrim(substring(s.slice, N.Number + 1,
                        charindex(@delim, s.slice, N.Number + 1) - N.Number - 1)))
                 FROM  Numbers N
                 JOIN  @slices s ON N.Number <= len(s.slice) - 1
                                AND substring(s.slice, N.Number, 1) = @delim) AS x

      RETURN
   END