SQL Server 2005: dynamically adding parameters to a stored procedure

Question

Scenario

I have a stored procedure that takes a single parameter. I want to update this stored procedure to take a VARIABLE NUMBER OF PARAMETERS - a number that I will never know. I currently use SQLConnections through a C# interface in order to pass in a single parameter to the stored procedure and return a result.

The SQL Part

Lets say that I have a stored procedure that returns a list of results based on a single input parameter "@ccy" - (Currency). Now lets say that I want to update this stored procedure to take a list of Currencies instead of a single one, but that this number will be variable depending on the situation.

The SQL Code

ALTER PROCEDURE [dbo].[SEL_BootStrapperInstRICs]
(
@ccy varchar(10)
)
AS

SELECT DISTINCT i.CCY, i.Instrument, i.Tenor, r.RIC, r.[Server], r.RIType
FROM MDR.dbo.tblBootStrapperInstruments as i, MDR.dbo.tblBootStrapperRICs as r
WHERE i.Instrument = r.MurexInstrument
AND
i.Tenor = r.Tenor
AND i.CCY = r.CCY
AND i.CCY = @ccy
AND r.RIType NOT LIKE '%forward%'

The C# Part

This particular stored procedure is called from a C# WinForms application that uses the "SqlCommand.Parameters.AddWithValue()" method. As mentioned earlier this method currently passes in a single Currency as the parameter to the stored procedure and returns the result as a DataSet.

    public DataSet GetBootStrapperInstRICsDS(List<string> ccys)
    {
        DataSet ds;
        SqlConnection dbConn = null;
        SqlCommand dbCmd = new SqlCommand();

        try
        {
            dbConn = GetSQLConnection();
            dbCmd = GetSqlCommand();
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.CommandText = Utils.Instance.GetSetting     ("SELBootStrapInsRics", "default");
            foreach(string ccy in ccys)
              dbCmd.Parameters.AddWithValue("@ccy", ccy);

            dbCmd.CommandTimeout = 600;
            dbCmd.Connection = dbConn;

            SqlDataAdapter adapter = new SqlDataAdapter(dbCmd);
            ds = new DataSet();
            adapter.Fill(ds, "tblBootStrapperInstRICs");

            dbCmd.Connection.Open();

            return ds;
        }
        catch (Exception ex)
        {
            ApplicationException aex = new ApplicationException        ("GetBootStrapperInstRICsDS", ex);
            aex.Source = "Dal.GetBootStrapperInstRICsDS " + ex.Message;
            MainForm.job.Log(aex.Source, Job.MessageType.Error);
            Job.incurredErrors = true;
            throw aex;
        }
        finally
        {
            if (dbCmd != null)
                dbCmd.Dispose();
            if (dbConn != null)
            {
                dbConn.Close();
                dbConn.Dispose();
            }
        }
    }

The Question

On the C# side I think my best option is to use a "foreach/for loop" in order to iterate through a list of parameters and dynamically add a new one to the SPROC. (I have already made this update in the C# code above).

HOWEVER - Is there some way that I can do this in the SQL Stored Procedure too? My thoughts are split with two potential options - Either create 20 or more parameters in the SPROC (each with the same name but with an incrementing number at the end e.g. - @ccy1,@ccy2 etc.) and use "for(int i=0;i

    for(int i=0;i<NumberOfCurrenciesToAdd;i++)
       dbCmd.Parameters.AddWithValue("@ccy"+i, currencyArray[i]);   

Or the other option is to do something completely different and less rubbish and hack-esque. Help greatly appreciated.

EDIT - SQL Server 2005

EDIT2 - Must Use SPROCS - Company Specification Requirement.

Answer 1

You never specified SQL Server version, but for 2008 there are Table-Valued Parameters, which may help you:

Table-valued parameters are a new parameter type in SQL Server 2008. Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Answer 2

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.