Displaying Detail Records for User-Selected Master Records :: Using a Dynamic IN ClauseBy Scott Mitchell
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,
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!
IN Keyword in a
A simple master/detail report, where the detail records for a single master record are displayed, can be accomplished using the following straightforward
Returning to the Northwind database's
Products tables, the following
SELECT statement would return
products that belonged to the Beverages category (which has a
CategoryID value of 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.
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.
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:
@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
(1, 2, 7), for instance - can be provided via a subquery that returns precisely one column. (A subquery is additional
SELECTstatement that appears within some portion of the "outer"
SELECTstatement.) Consider the following query:
SELECT CategoryID FROM Categories WHERE CategoryName LIKE 'C%' returns a list of
CategoryID values for those
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:
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:
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
FROM clause. Consider the following SQL query:
This returns the following results:
To get back just the values from the passed-in comma-delimited list as(and not
The above query returns just the
Value column from the UDF's return table. The
Value column is of type
Because we know that the values passed into the UDF are integers, however, we can cast the result accordingly.
SELECT statement that uses the
udf_Split UDF can also be used to specify the set values on the right of the
keyword. What's more, the UDF happily accepts parameters as its inputs.
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
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
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:
The code starts by creating a List of strings (
CategoryIDList). It then enumerates through the items in the
CheckBoxList and for each selected item adds its
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 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
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
@SelectedCategoryIDs parameter. This is best done in the SqlDataSource control's
Selecting event handler, which fires just
SELECT query is sent off to the database.
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
(as well as the syntax for setting a parameter value for an ObjectDataSource) see Examining
the Data Source Control's Events.
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!
INKeyword (technical docs)