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