Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server
Article

Passing a Comma Delimited Parameter to a Stored Procedure

Rate me:
Please Sign up or sign in to vote.
3.88/5 (19 votes)
29 Jan 20044 min read 143.4K   31   9
Passing multiple values as a single 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.

SQL
/* 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.

SQL
/* 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.

SQL
/* 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:

SQL
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.

SQL
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.

SQL
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.

SQL
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.

SQL
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

Now lets execute the stored procedure and view the results.
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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer (Senior)
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralAlternative Pin
schoonvd@Realsoftware.be3-Feb-04 0:04
schoonvd@Realsoftware.be3-Feb-04 0:04 
Hi,

great article but, there is an alternative which gives you more flexibility about what information you can pass to the stored procedure. Pass in the string containing an XML, then parse the string with OPENXML, and from there on, continue with the rest of your processing having a kind of temporary table.

for details about OPENXML, refer to BOL
GeneralRe: Alternative Pin
-Dr_X-4-Feb-04 16:21
-Dr_X-4-Feb-04 16:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.