When you need to pass a parameter to a stored procedure with a variable number of values you don't have the option of using an array or pointer type mechanism, which is found in other programming languages. The solution many people use is to pass in a chain long enough to house a collection of values with each value separated by commas or another identifier. Once inside the stored procedure, you often use a loop to split the string and create a temporary table containing the different values. This temporary table will be used later in the queries of the stored procedure.

The Loop Version

For those of us that come from procedural programming languages such as VB or C + +, the strategy of using the loop to split the string is natural: we look for the first delimiter's occurrence and, if found, we separate the string into two parts, the first being one of the parameter's values. The remaining string contains the rest of the values, to be processed in the next iteration. When there are no more delimiters, the string contains the last value and once processed, the loop ends.

-- Let's imagine this is the parameter ----
Declare @sParameter varchar(8000)
Set @sParameter = 'WA,AZ,CA,TS'
-- Table to get the incoming parameters ----
-- (sorry Christine, it was cut-and-pasted in the sample below) ----
Declare @tStates table ( state char(2) )
-- Now the loop ----
Declare @iPos integer
Set @sState varchar(8000)
Set @iPos = CharIndex( ',', @sParameter )
While @sParameter <> ''
Begin
    If @iPos > 0 
    Begin
        Set @sState = Substring( @sParameter, 1, @iPos - 1 )
        Set @sParameter = Substring( @sParameter, @iPos + 1, 8000 )
    End
    Else
    Begin
        Set @sState = @sParameter
        Set @sParameter = ''
    End
    -- Insert here code to validate @sState ----
    Insert @tStates Values ( @sState )
    Set @iPos = CharIndex( ',', @sParameter )
End
Select * 
From @tStates

While being completely valid, I dislike this code: even without the validating section. This is too much coding for so little functionality. I always suspected I was missing something in SQL. As we are dealing with a set of values, we'd like to use some set-based code to simplify the whole operation. What if we could do parsing and validation with a single SQL sentence? Next we'll see a way to achieve it.

The SQL version

From now on assume that we have a table against which to validate the incoming parameter values. With this table, it is easy to insert all the values in only one insert. We use the CHARINDEX function to find the existence of every row of the table in the parameter string. If CHARINDEX returns a number bigger than zero, we have found a match and then we insert the column in the table in the new table of passed parameters. No loop, only one sentence: concise and elegant code.

-- Imagine this table exists previously in your database ----
Declare @T_STATES table ( state char(2) )
Insert @T_STATES Values ( 'AZ' )
Insert @T_STATES Values ( 'CA' )
Insert @T_STATES Values ( 'MT' )
Insert @T_STATES Values ( 'TX' )
Insert @T_STATES Values ( 'WA' )
Insert @T_STATES Values ( 'TS' )
-- Suppose this is the parameter ----
Declare @sParameter varchar(8000)
Set @sParameter = 'WA,AZ,CA,TS'
Declare @tStates table ( state char(2) )
Insert @tStates 
Select state
From @T_STATES
Where CharIndex( state, @sParameter ) > 0
Select * 
From @tStates

Note that values we store come from the validating table, not from the parameter string. That means you'll get valid values: no issue with misspelling input parameters, nor with repeated values as in:

Declare @sParameter = 'WA,AZ,WA,TS'

In this case, as we loop the @T_STATES table, we'll find only one row with value = 'WA' because CHARINDEX detects the first occurrence only. So, the output table ends acting as if we had a DISTINCT clause on the parameter string. We'll get WA, ZA and TS values only.

Fine tuning

What if there are values in the table that are substrings of other values? In real life I have a table named T_Services, with several values like STB and STBC. Obviously such values are such that one is part of the other. If I pass a parameter like 'STBC,...', the output will include both values, STB and STBC.

Declare @T_SERVICES table ( service varchar(4) )
Insert @T_SERVICES Values ( 'STBC' )
Insert @T_SERVICES Values ( 'STB' )
Insert @T_SERVICES Values ( 'RDSI' )
Insert @T_SERVICES Values ( 'CTL' )
Declare @sParameter varchar(8000)
Set @sParameter = 'STBC,RDSI'
Declare @tServices table ( service varchar(4) )
Insert @tServices 
Select service
 From @T_SERVICES
 Where CharIndex( service, @sParameter ) > 0
Select * 
From @tServices
-- We get STBC, RDSI... and STB!!! ---

Why? As CHARINDEX only deals with string occurrences, it happens that STB string is contained in STBC, so from this point of view STB is also a valid parameter value. To avoid this annoyance, we modify the search to include the delimiters, both the trailing and ending commas In this case.

Where CharIndex( ',' + state + ',', @sParameter ) > 0

For this strategy to work properly, we must add also trailing and ending commas in input parameter:

Declare @sParameter varchar(8000)
Set @sParameter = 'STBC,RDSI'
Set @sParameter = Case When Left( @sParameter, 1 ) <> ',' Then ',' Else '' End +
 @sParameter + 
 Case When Right( @sParameter, 1 ) <> ',' Then ',' Else '' End
-- The value now is ',STBC,RDSI,' ----

Perhaps you'll have also to remove the blanks in the input string, as they may interfere with detection of delimited values.

Declare @sParameter varchar(8000)
Set @sParameter = 'STB  ,  RDSI'    -- We won't find matches with this
Set @sParameter = Replace( @sParameter, ' ', '' )

However, things as repeated commas or misspelled values will not affect the correct operation. These are the benefits of focusing the table of valid values as main driver (i.e. the subject of the FROM clause) instead of the incoming string (as we did in the former loop).

Another refinement to end: if the user pass a non-matching parameter, you can return an info to get him aware of it.

Declare @sParameter varchar(8000)
Set @sParameter = 'XX,YY,ZZ'
Declare @tStates table ( state char(2) )
Insert @tStates 
Select state
From @T_STATES
Where CharIndex( state, @sParameter ) > 0
If @@RowCount < 1
Begin
    Select 'No matches. Valid values are:' As InfoMessage
    Union All
    Select state
    From @T_STATES
    Return
End

Conclusion

This article shows how to use SQL code to parse and validate a string containing multiple comma separated values. The core of the technique is traverse the table of valid values using the CHARINDEX function to find matches in the incoming string. Additional benefits are implicit validation and lax syntax requirements for the input string.