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