Displaying Detail Records for User-Selected Master Records :: Using a Dynamic IN Clause
By Scott Mitchell
Introduction
The master/detail report pattern is used to display information in two database tables that share a one-to-many relationship. Typically the user is shown a list of the master records and can pick one to view the corresponding detail records. Consider the Northwind database that models product information using two tables,
Categories
and Products
, where each product is assigned to precisely one category. Categories represent the master
records and products the detail records. In a master/detail report, the user would choose a category to see the products that belong to it.
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
A simple master/detail report, where the detail records for a single master record are displayed, can be accomplished using the following straightforward
SELECT
statement:
SELECT ColumnList
|
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
|
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
|
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
|
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
|
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
The set values listed to the right of the
IN
keyword - (1, 2, 7)
, for instance - can be provided via a subquery that returns
precisely one column. (A subquery is additional SELECT
statement that appears within some portion of the "outer" SELECT
statement.)
Consider the following query:
SELECT ColumnList
|
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)
Since SQL Server 2000 developers have been able to create User Defined Functions (UDFs). UDFs are similar to functions in a normal programming language in that they can accept a variable number of input parameters and can optionally return a scalar or tabular result. The body of a UDF is made up of T-SQL statements. For more information on creating and using UDFs, see the SQLTeam.com articles Introduction to User Defined Functions and User Defined Functions. There's also a great column by John Papa in the November 2003 issue of MSDN Magazine on the topic: SQL Server User Defined Functions.
Putting It All Together: A "Split" User Defined Function
What we need is some mechanism by which we can turn a comma-delimited list of values into a one-column table with a row for each value in the list. Such functionality would allow us to write a SQL statement like the following:
SELECT ColumnList
|
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
|
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 *
|
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)
|
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
|
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
The download available at the end of this article includes a demo of displaying detail records that belong to the selected master records (see
ProductsByCategory.aspx
). Specifically, the demo lists the categories in a CheckBoxList control. After selecting the categories to display
and clicking a Button control, the page posts back and displays the corresponding products in a GridView.
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
|
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)
|
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
|
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
At this point we have a fully functional master/detail report where the user can choose multiple master records. I use this pattern in many of my web applications. If users frequently view this report for a particular set of categories, it becomes tedious and time consuming for them to have to check the same categories each time they visit the page. Assuming users viewing the report are authenticated users, we can save their preferences and have them automatically selected whenever they view this report page. The second installment of this article series examines how to accomplish this.
Read the second installment: Saving the User's Search Preferences
Until then... Happy Programming!
Further Reading
IN
Keyword (technical docs)