SQL Queries and Comma-Delimited Lists

Developers may encounter a need to pass in, as a single parameter, a comma-delimited list of values to a SQL query or stored procedure. Unfortunately, SQL parameters don't have a way to represent comma-delimited lists. Here are two solutions, one for MS SQL Server and the other for Oracle, that solve this problem, using a sub-query and the IN search qualifier respectively.

The following query is for MS SQL Server, where @sel_customers is the parameter with the delimited list of values:

  -- my stored procedure

  @sel_customer varchar(200)   -- this is the parameter

  SELECT SalesOrderID, OrderDate, CustomerID
  FROM AdvWorks.Sales.SalesOrderHeader
  WHERE CustomerID IN
    (SELECT CustomerID FROM AdvWorks.Sales.Customer WHERE CHARINDEX(STR(CustomerID), @sel_customers) > 0)

With this technique, the SQL statement does not vary between executions and will therefore be cached. By putting the expression into a sub-query, the DBMS optimizer will only execute it once (because it's not correlated). Since the sub-query just accesses a lookup table, the speed will be very fast as opposed to evaluating the expression for each row of the primary query table.

The example above shows a query in a stored procedure, but the same query could be used by itself, replacing the parameter with an @Request token.

Here's a similar query for use with an Oracle DB:

  SELECT ACTIVITY_ID, CODE_VALUE, CATEGORY_ID,
  FROM SYSADM.ACTIVITY_TYPE
  WHERE CATEGORY_ID IN
   (SELECT CATEGORY_ID
   FROM SYSADM.ACTIVITY_CATEGORY
   WHERE instr(:@sel_ActivityCategories, '''' || to_char(CATEGORY_ID) || '''') > 0 )

This sub-query is slightly more complex because quotes are needed around the search argument (CATEGORY_ID) in order to prevent partial key matches (e.g. the key value '1' is part of the key '123'). This isn't an issue in MS SQL Server because Microsoft conveniently adds a space at the beginning of the number for sign, therefore eliminating partial key matches.

Back to the top

SQL Queries and Comma-Delimited Lists

Recently viewed articles