To read the article online, visit http://www.4GuysFromRolla.com/articles/070908-1.aspx

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:

IdValue
11
22
37

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 products in the Beverages, Condiments, and Produce categories are displayed.

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.

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

Until then... Happy Programming!

  • By Scott Mitchell


    Further Reading


  • The IN Keyword (technical docs)
  • Using Sets with SQL Server
  • Tutorials on Creating Master/Detail Reports
  • Creating a User Defined Function (UDF) in SQL for Comma-Delimited Searches
  • Displaying Detail Records for User-Selected Master Records :: Saving the User's Search Preferences
  • Attachments


  • Download the code used in this article

  • Article Information
    Article Title: ASP.NET.Displaying Detail Records for User-Selected Master Records :: Using a Dynamic IN Clause
    Article Author: Scott Mitchell
    Published Date: July 9, 2008
    Article URL: http://www.4GuysFromRolla.com/articles/070908-1.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers