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.