Comma-Delimited Value to Table

Overview

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'