Displaying Detail Records for User-Selected Master Records :: Using a Dynamic IN Clause

Introduction

Imagine, however, that instead of letting the user pick one category we needed to let them pick any number of categories and then show the products that belonged to any of those selected categories. SQL includes an IN keyword that can be used to return detail records within a set of master records. Unfortunately, there are challenges using the IN keyword with values supplied at runtime. The good news is that with a little bit of work, we can overcome these limitations.

This article is the first in a two-part series that looks at displaying detail records from a set of user-selected master records. This first part looks at extending the IN keyword functionality to allow for the parameters in the IN keyword to be supplied at runtime. The second part shows how to save what master records the user wants to search on by default and how to have these selections automatically applied when visiting the report page. Read on to learn more!

Using the IN Keyword in a WHERE Clause

SELECT ColumnList FROM DetailsTable WHERE ForeignKeyColumn = MasterPrimaryKeyValue

Returning to the Northwind database's Categories and Products tables, the following SELECT statement would return products that belonged to the Beverages category (which has a CategoryID value of 1).

SELECT ColumnList FROM Products WHERE CategoryID = 1

SQL includes an IN keyword that determines whether a particular value is within a set of values. This keyword can be used to return details records that exist in a set of master records.

SELECT ColumnList FROM DetailsTable WHERE ForeignKeyColumn IN (MasterPrimaryKeyValue1, MasterPrimaryKeyValue2, ..., MasterPrimaryKeyValueN)

The IN keyword returns True if the value on the left (ForeignKeyColumn) is found in the set of items on the right (MasterPrimaryKeyValue1, MasterPrimaryKeyValue2, ..., MasterPrimaryKeyValueN). For example, the following query returns the products in the Beverages, Condiments, or Produce categories, which have CategoryID values of 1, 2, and 7, respectively.

SELECT ColumnList FROM Products WHERE CategoryID IN (1, 2, 7)

As the above example shows, the set values on the right of the IN keyword can be entered as a hard-coded, static, comma-delimited list. However, in a master/detail report we would not want to use a hard-coded list of CategoryID values, but rather have the values determined by the selections made by the user. A hopeful developer might try using a parameterized query like so:

SELECT ColumnList FROM Products WHERE CategoryID IN (@ListOfCategoryIDs)

Where @ListOfCategoryIDs is a string parameter that contains a comma-delimited list of CategoryID values. Unfortunately, the set values to the right of the IN keyword cannot be dynamically supplied at runtime via a parameter. Granted, the above syntax will "pass" a SQL syntax checker and the query will work as expected if @ListOfCategoryIDs contains a single CategoryID value, but it will result in an error if you pass in multiple values, like '1, 2'.

Despite this setback, there is a workaround! Before we examine it there are two related topics that we need to discuss.

Specifying the Set Values to the Right of the IN Keyword Using a Subquery

SELECT ColumnList FROM Products WHERE CategoryID IN (SELECT CategoryID FROM Categories WHERE CategoryName LIKE 'C%')

The subquery SELECT CategoryID FROM Categories WHERE CategoryName LIKE 'C%' returns a list of CategoryID values for those categories whose CategoryName column starts with the letter C. This subquery returns the Condiments and Confections categories, which have CategoryID values of 2 and 3, respectively. Consequently, the outer SELECT query returns all products that belong to either of these two categories.

What's important to take away from this example is that the set values to the right of the IN keyword are specified a tabular data comprised of one column. When the values are supplied as hard-coded literals - 1, 2, 7, for instance - SQL Server handles turning that into a tabular representation, namely a one-column table with three records. The subquery accomplishes the same thing. It returns a one-column table with (in this case) two records, Condiments and Confections.

Creating Table-Value User Defined Functions (UDFs)

Putting It All Together: A "Split" User Defined Function

SELECT ColumnList FROM Products WHERE CategoryID IN (TurnCommaDelimitedListIntoTable(@ListOfCategoryIDs))

The TurnCommaDelimitedListIntoTable magic would convert the string parameter @ListOfCategoryIDs, which might have the value '1, 2, 7', into a one-column table with rows for each value, three in this case: 1, 2, and 7. There does not exist a built-in function that does this, but we can certainly build our own User Defined Function. In fact, Corey Albedol has already created such a UDF and shared it here on 4Guys: Creating a User Defined Function in SQL for Comma-Delimited Searches. I encourage you to read Corey's article, as he does a great job of explaining how the UDF works. For this article, I'm going to just wave my hands in the air and say, "It works," and leave it at that. If you want a more in-depth look, read Corey's article.

To add the UDF to your database, open a query window, paste in the following T-SQL, and execute it:

CREATE FUNCTION dbo.udf_Split (    @List nvarchar(2000),    @SplitOn nvarchar(5) ) RETURNS @RtnValue table (    Id int identity(1,1),    Value nvarchar(100) ) AS    BEGIN    While (Charindex(@SplitOn, @List) > 0)    Begin       Insert Into @RtnValue (value)       Select Value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn, @List) - 1)))       Set @List = Substring(@List,Charindex(@SplitOn, @List) + len(@SplitOn), len(@List))    End    Insert Into @RtnValue (Value)    Select Value = ltrim(rtrim(@List))    Return END

The udf_Split UDF accepts two string parameters as input - the list and a delimiter. It returns a table-valued result that contains two columns - Id, an auto-incrementing identifier, and Value. Because this UDF returns a tabular result, you can use it in the FROM clause. Consider the following SQL query:

SELECT * FROM dbo.udf_Split('1,2,7', ',')

This returns the following results:

Id	Value
1	1
2	2
3	7

To get back just the values from the passed-in comma-delimited list as(and not Id), use:

SELECT CAST(Value AS int) FROM dbo.udf_Split('1,2,7', ',')

The above query returns just the Value column from the UDF's return table. The Value column is of type nvarchar(100). Because we know that the values passed into the UDF are integers, however, we can cast the result accordingly.

Value
1
2
7

The above SELECT statement that uses the udf_Split UDF can also be used to specify the set values on the right of the IN keyword. What's more, the UDF happily accepts parameters as its inputs.

SELECT ColumnList FROM Products WHERE CategoryID IN (SELECT CAST(Value AS int) FROM dbo.udf_Split(@ListOfCategoryIDs, ','))

We now have a way to query detail records from a set of master records whose values are specified at runtime. Now that we've got the SQL part conquered, all that remains is to create the reporting web page.

Displaying the Details Records Belonging to the Selected Master Records

A screen shot of the demo in action is shown below. The user has selected the categories Beverages, Condiments, and Produce, and the results are displayed in the grid below.

The GridView's data is populated using the following SELECT query (which is defined in the SqlDataSource control ProductsBySelectedCategoriesDataSource):

SELECT Products.ProductName, Categories.CategoryName, Products.QuantityPerUnit, Products.UnitPrice FROM Products    INNER JOIN Categories ON       Products.CategoryID = Categories.CategoryID WHERE Products.CategoryID IN (SELECT CAST(Value as int) FROM dbo.udf_Split(@SelectedCategoryIDs, ',')) ORDER BY ProductName

As you can see, it limits the results to those products whose CategoryID values fall within the set returned by splitting on the @SelectedCategoryIDs parameter. But how and where does this parameter value get set? Clearly, the @SelectedCategoryIDs parameter needs to be set to a comma-delimited list of CategoryID values corresponding to the categories selected by the user in the CheckBoxList. This list can be constructed via the following code:

Dim CategoryIDList As New List(Of String) For Each cb As ListItem In CategoryList.Items    If cb.Selected Then       'The Category was "checked"; add it's CategoryID value to CategoryIDList       CategoryIDList.Add(cb.Value)    End If Next 'Create a comma-delimited string from CategoryIDList Dim commaDelimitedString As String = String.Join(",", CategoryIDList.ToArray())

The code starts by creating a List of strings (CategoryIDList). It then enumerates through the items in the CategoryList CheckBoxList and for each selected item adds its Value to CategoryIDList. (The Value property of each CheckBoxList item is mapped to the CategoryID value via the CheckBoxList's DataValueField property setting.) After collecting the selected CategoryID values, the List is converted to a comma-delimited string using the String.Join method. String.Join method takes two input parameters, the delimiter and an array of strings, and generates a new string that contains the elements in the array separated by the delimiter.

The above code turns the user's selected categories into a comma-delimited list of CategoryID values. All that remains is to assign it to the @SelectedCategoryIDs parameter. This is best done in the SqlDataSource control's Selecting event handler, which fires just before the SELECT query is sent off to the database.

Protected Sub ProductsBySelectedCategoriesDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles ProductsBySelectedCategoriesDataSource.Selecting    'Set the @SelectedCategoryIDs parameter's value    'First, tally up the CategoryIDs from CategoryList    ... Code Removed for Brevity ...    'Set the parameter value     e.Command.Parameters("@SelectedCategoryIDs").Value = commaDelimitedString End Sub

The syntax for setting a parameter in the Selecting event depends on the data source control being used. When using a SqlDataSource control, reference the parameter via the e.Command.Parameters collection as shown above. For more information on the Selecting event (as well as the syntax for setting a parameter value for an ObjectDataSource) see Examining the Data Source Control's Events.

Looking Forward

Read the second installment: Saving the User's Search Preferences

Until then... Happy Programming!

Attachments