|
|
One of the frequently asked questions in a lot of SQL Server forums is how to
handle a comma-delimited value passed as a parameter in a stored
procedure. To better illustrate the question, let's say you have a
website wherein you have a page that lets the users do a search of lawyers in
their area. The page lets the user enter the ZIP code they are interested
in and displays a selection of different types of lawyers.
In your database you have a table containing the different types of lawyers and
the following are sample records in that table (let's call the table
[dbo].[Lawyer Types]).
|
ID Name Description
----- ------------------------------ -----------------------------------------------------
1 Accidents and Injuries Lawyer Personal Injury, Car Accidents, Medical Malpractice
2 Business Lawyer Collections, Incorporation, Litigation, Patents
3 Criminal Lawyer Traffic Violations, White Collar Crime
4 Dangerous Products Lawyer Asbestos, Drug Recalls, Molds
5 Divorce and Family Lawyer Adoption, Child Custody, Child Support, Divorce
6 Employee's Rights Lawyer Civil Rights, Discrimiation, Sexual Harassment
7 Estate Planning Lawyer Estate Planning, Living Wills, Trusts, Wills
In your web page, instead of displaying this list of lawyer types in a drop-down
list or in a radio-button list, you displayed it as a group of checkboxes
letting the user select multiple lawyer types at the same time. When the
user selects more than one lawyer type, you create a comma-delimited value
containing the IDs of the selected records.
For example, if the user selects the "Accidents and Injuries Lawyer" and the
"Dangerous Products Lawyer", the web application will get the corresponding IDs
for these records and create a comma-delimited string, which will then be
passed to a stored procedure in SQL Server for processing. The string
that will be passed to the stored procedure for the selected lawyer type will
be "1,4".
Dynamic SQL Statement
One way of handling comma-delimited value parameters is by using dynamic
SQL. Here's how the stored procedure that will retrieve all lawyers of
the given lawyer type in the provided ZIP code will look like using dynamic
SQL.
CREATE PROCEDURE [dbo].[GetLawyers] ( @ZIP CHAR(5), @LawyerTypeIDs VARCHAR(100) )
AS
DECLARE @SQL VARCHAR(2000)
SET @SQL = 'SELECT * FROM [dbo].[Lawyers]
WHERE [ZIP] = ' + @ZIP + ' AND
[LawyerTypeID] IN (' + @LawyerTypeIDs + ')'
EXECUTE (@SQL)
GO
To execute the stored procedure passing the ZIP code entered by the user and the
selected lawyer types in a comma separated value:
EXECUTE [dbo].[GetLawyers] '12345', '1,4'
Table-Valued User-Defined Function
Another method is to create a user-defined function that will convert the comma
separated value into a table that can then be used to join with the
[dbo].[Lawyers] table. Below is a table-valued user-defined function that
takes a comma-delimited value and returns a table containing these values.
CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN
DECLARE @String VARCHAR(10)
WHILE LEN(@StringInput) > 0
BEGIN
SET @String = LEFT(@StringInput,
ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
LEN(@StringInput)))
SET @StringInput = SUBSTRING(@StringInput,
ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
LEN(@StringInput)) + 1, LEN(@StringInput))
INSERT INTO @OutputTable ( [String] )
VALUES ( @String )
END
RETURN
END
GO
To use it in the stored procedure that retrieves the lawyers, here's how it will
look like:
CREATE PROCEDURE [dbo].[GetLawyers] ( @ZIP CHAR(5), @LawyerTypeIDs VARCHAR(100) )
AS
SELECT Lawyer.*
FROM [dbo].[Lawyers] Lawyer INNER JOIN [dbo].[ufn_CSVToTable] ( @LawyerTypeIDs ) LawyerType
ON Lawyer.[LawyerTypeID] = LawyerType.[String]
WHERE Lawyer.[ZIP] = @ZIP
GO
Executing the stored procedure is the same way as the previous method:
EXECUTE [dbo].[GetLawyers] '12345', '1,4'
|