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.
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.
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.
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'
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)
)
AS
CREATE TABLE #1
(
IDList Int
)
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
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.