Handling lists of parameters in SQL

During development you might want to pass a list of entities to a query or command. The recommended and safest way is to wrap every item in a parameter and pass it to the query. The problem is that the number of parameters that can be passed to a user function, stored procedure or even dynamic query is limited. The first two are set to 2100 entries per documentation. The third can vary based on the sql server version and the size of the actual parameters. In Sql Server 2008 it’s 65,536 * size of the network packet.

There are numerous way of dealing with this and I’ll describe two possible solutions and one how to not do this, so let’s start.

NOT TO: Dynamically built query

So the first and rather obvious idea would be to dynamically create a query and then pass it to the executor, however don’t ever do that unless you really know what you’re doing and the data doesn’t come from the users. Even using sanitizing the filter strings the query could be error-prone to SQL injection, which would be disaster once deployed to production server.

User Defined Types

From the 2008 version of the SQL Server, there’s a new featured called a User Defined Table Type. Here is an example of how to use it:

Create your User Defined Table Type:

CREATE TYPE [dbo].[ContractList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

Next you need to use it properly in your stored procedure:

CREATE PROCEDURE [dbo].[sp_UseContractList]
    @list ContractList READONLY
AS
BEGIN
    SELECT l.Item FROM @list l;
END

Finally here’s some sql to use it in c#:

using (var connection = new SqlConnection(connectionString))
{
    connection .Open();

    using (SqlCommand cmd = new SqlCommand("exec sp_UseContractList @list", con))
    {
        using (var table = new DataTable()) {
          table.Columns.Add("Item", typeof(string));

          for (int i = 0; i < 20; i++)
            table.Rows.Add("Item " + i.ToString());

          var pList = new SqlParameter("@list", SqlDbType.Structured);
          pList.TypeName = "dbo.ContractList";
          pList.Value = table;

          cmd.Parameters.Add(pList);

          using (var dr = cmd.ExecuteReader())
          {
            while (dr.Read())
                Console.WriteLine(dr["Item"].ToString());
          }
         }
    }
}

To execute this from SSMS

DECLARE @list AS StringList

INSERT INTO @list VALUES ('Contract1')
INSERT INTO @list VALUES ('InvalidContract2')
INSERT INTO @list VALUES ('Contract3')

EXEC sp_UseContractList @list

Batches

Since the server can handle up to 2000 parameters, we can divide the query execution into batches and save the result in the memory until we eventually run out of inputs, here’s some code:

CREATE PROCEDURE [dbo].[sp_UseContractList] 
  @list ContractList READONLY 
AS 
BEGIN 
  SELECT l.Item FROM @list l; 
END

public IEnumerable<ContractDTO> GetContractsByNumbers(IEnumerable<string> contractNumbers)
        {
            using (DataAccessCommand command = DataAccess.SqlCommand())
            {
                IList<ContractDTO> contracts = new List<ContractDTO>();

                var contractParams = new List<string>();
                int index = 0;
                foreach (var contractNumber in contractNumbers)
                {
                    var paramName = "@sContractNumber" + index;
                    contractParams.Add(paramName);
                    command.AddParameter(paramName, contractNumber);
                    index++;

                    if (contractParams.Count >= 500)
                    {
                        contracts = contracts.Concat(GetContracts(command, contractParams)).ToList();
                        command.ClearParameters();
                        contractParams.Clear();
                        index = 0;
                    }
                }

                if (contractParams.Count > 0)
                {
                    contracts = contracts.Concat(GetContracts(command, contractParams)).ToList();
                }
                return contracts;
            }
        }

Concatenated Parameter Parsing

Yet another solutions is to create one parameter, which is then passed to a stored procedure or function and splitted by the some delimiting user function. The reason why I don’t like this solution is that we always have to define some delimiter and the user input/business requirements could change, so in most cases we should expect the items could change. Therefore this approach has two issues:

1. It’s error prone, because of the unpredicatable effect it could cause if the developer, who designed this delimiting function is no longer employed or simply the codebase is quite old.
2. Whenever there’s a change in the business requirements regarding the item format, we need to remember about the SQL procedure and update it. It’s not really breaking the single responsibility princible, because it could be extracted from the write procedure, but still there’s some piece of logic in the db that we cannot debug easily from the IDE, if anything breaks.