Passing a Comma Delimited Parameter to a Stored Procedure

Introduction

There have been many times I have wanted to pass an In Clause to a Stored Procedure to return a sub-set of data with a single parameter having multiple values. However, and as you probably already know, it is just not that easy unless you are using Dynamic SQL. If it is a single value already linked to a column that can filter the grouping of the data then there is no problem.

Here is an example. Suppose we have many Salesmen in the Southeast Region. Obviously the there would be a Region table linking all of the salesmen for the region. But what if we just wanted to know which cities a selected list of top salesman work out of. Well now there it becomes a dilemma, especially if the front end is using a stored procedure to retrieve the list of cities from their selected list of salesman. Sure we could have a stored procedure that return a list of salesman from an office, state or region. But to return a list of cities based upon a user selected list of salesman with out opening the query analyzer could be quite difficult. Wouldn't it be nice to simply pass a comma- delimited list of SalesmanID's to the procedure to retrieve all of the Salesman and their respective cities?

Well here is your answer. Now some of this code was obtain from various posts on many different sites so I will take no claim for all it. If by chance some of the code is yours, just send me an email with the link to your posted code and I will list you as part of the credits. I am sorry if you are offended with some of code being yours. However, I thought it would be very useful to many developers as it became quite useful to me.

Implementing the code

Step 1

We will need to create a permanent table in the database to keep up with the counts. It is a simple single column table, which will need to be populated with an incremented integer. I called my table tblToolsStringParserCounter and populate the rows with values 1 to 1000. You could do more if needed. Not sure about the performance though. I haven't needed to pass any more that a half dozen or so comma delimited values.

Okay, lets first open up the SQL Query Analyzer. We first need to run the following code to CREATE a test database and execute a USE statement.

/* Script to Create Database */
CREATE DATABASE TestParserLogic
GO
USE TestParserLogic
GO

Now lets create the parser counter table. This table is will be used in parsing the comma delimited parameter sent to the stored procedure.

/* Script to create the table */
CREATE TABLE tblToolsStringParserCounter
(
    ID INT
)

We need still to populate the table. This will only need to be done once unless the table is dropped from the database. I use a loop from 1 to 1000 and insert each value during the loop.

/* Populate the table */
DECLARE @i INT
SELECT    @i = 1

WHILE (@i <= 1000)
BEGIN
    INSERT INTO tblToolsStringParserCounter SELECT @i
    SELECT @i = @i + 1
END
GO

Step 2

Now that the table is created lets demonstrate an example of how it can be very useful. I am not going to dissect the query below. However, its responsibility is to parse the comma delimited string with help of the newly created table above.

Here is the query that parses the comma delimited string into rows:

SELECT  Convert(Int, NullIf(SubString(',' + @IDs + ',' , 
  ID , CharIndex(',' , ',' + @IDs + ',' , ID) - ID) , '')) AS IDList 
FROM    tblToolsStringParserCounter
WHERE   ID <= Len(',' + @IDs + ',') AND 
  SubString(',' + @IDs + ',' , ID - 1, 1) = ',' 
AND     CharIndex(',' , ',' + @IDs + ',' , ID) - ID > 0 

The results of the statement resemble the output from executing single column select statement. Now let's give the query a try.

DECLARE @IDs varchar(100)
SELECT  @IDs = '429,446,552,1001, 332 , 471' 
    --Any IDs as an example


SELECT  Convert(Int, NullIf(SubString(',' + @IDs + 
  ',' , ID , CharIndex(',' , ',' + @IDs + ',' , ID) - 
  ID) , '')) AS IDList 
FROM    tblToolsStringParserCounter
WHERE   ID <= Len(',' + @IDs + ',') AND SubString(',' + 
  @IDs + ',' , ID - 1, 1) = ',' 
AND     CharIndex(',' , ',' + @IDs + ',' , ID) - ID > 0  

Here is the output from the above SQL script.

IDList      
----------- 
429
446
552
1001
332
471

Notice that the SQL statement is pretty forgiving to the spaces and missing ending comma in the @IDs variable passed to the SQL String. This is great for SQL developers who have to design stored procedures for the front-end developers.

Step 3

Now lets make up some test data by creating tables salesman and cities and populating it with data.

CREATE TABLE tblCity
(    CityID Int IDENTITY (1, 1) NOT NULL,
  City varchar(12) NOT NULL
)
GO
INSERT INTO tblCity (City) VALUES ('Houston')
INSERT INTO tblCity (City) VALUES ('New Orleans')
INSERT INTO tblCity (City) VALUES ('Atlanta')
INSERT INTO tblCity (City) VALUES ('Orlando')

CREATE TABLE tblSalesman
(    SalesmanID Int IDENTITY (1, 1) NOT NULL,
  SalesmanName varchar(10) NOT NULL,
    CityID Int NOT NULL,
)
GO
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('George', 1)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Mark', 2)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Greg', 3)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Susie', 4)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Kevin', 3)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Bobby', 1)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Terry', 1)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Betty', 2)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Carl', 2)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Gary', 4)

Verify the results of the newly created tables and data.

Query the data with a simple select statement.

SELECT  SalesmanName, City 
FROM    tblSalesman s,
        tblCity c
WHERE   s.CityID = c.CityID

Results from the above query.

SalesmanName City         
------------ ------------ 
George       Houston
Mark         New Orleans
Greg         Atlanta
Susie        Orlando
Kevin        Miami
Bobby        Houston
Terry        Houston
Betty        New Orleans
Carl         New Orleans
Gary         Orlando

Step 4

Now we need to create a procedure to query all of the cities from a comma delimited list of SalesmanIDs. With in the procedure we will need to create a temp table, #1, to hold the IDs passed to the procedure.

Remember the user would like to know what Cities each of their selected Salesman are from.

CREATE PROCEDURE sp_CityBySalesman
(
    @IDs as varchar(100) --SalesmanIDs

)
AS

    CREATE TABLE #1
    (
        IDList Int
    )
    -- Optional index on the temp table

    CREATE INDEX idx1 ON #1 (IDList)

    INSERT INTO #1
    SELECT  Convert(Int, NullIf(SubString(',' + @IDs + ',' , ID ,
     CharIndex(',' , ',' + @IDs + ',' , ID) - ID) , '')) AS IDList 
    FROM    tblToolsStringParserCounter
    WHERE   ID <= Len(',' + @IDs + ',') AND SubString(',' + 
      @IDs + ',' , ID - 1, 1) = ',' 
    AND     CharIndex(',' , ',' + @IDs + ',' , ID) - ID > 0  

    /* 
       Now use the temp table, #1 containing the list of SalesmanIDs 
       passed by the parameter @IDs, to obtain all of the Salesman's 
       cities respectively in the final select statement of the procedure

    */

    SELECT  SalesmanName, City
    FROM    tblSalesman s,
            #1 t,
            tblCity c
    WHERE   s.CityID = c.CityID
    AND     t.IDList = s.SalesmanID

GO

Step 5

sp_SalesmanByCity '1,3,5, 6 ,9 '

Notice the parameter passed has spaces and missing ending comma.

Results:

SalesmanName City         
------------ ------------ 
George       Houston
Bobby        Houston
Carl         New Orleans
Greg         Atlanta
Kevin        Miami

Points of Interest

I implemented the code writing an application in ASP.Net, VB.Net & SQL Server. Users can have rights to view data form multiple offices simultaneously. Since the user rights are cache this seemed to be the easiest way to implement the business rules for the application.

This is my first article to be posted on The Code Project. I hope it was helpful and informative. My next potential article will be on Cross-Tab Queries in SQL Server.