Using a Dynamic IN Clause With Master-Detail Records

Using a Dynamic IN Clause With Master-Detail Records - Demo

This example is based on Scott Mitchell's article at Displaying Detail Records for User-Selected Master Records :: Using a Dynamic IN Clause

I frequently have the need to pass a dynamically generated list of record IDs to a query or stored procedure either to select or delete a list of records. Unfortunately, something as simple as this does not work in SQL Server.

SELECT MovieID
FROM Movies
WHERE MovieID IN (@ListOfMovieIDs)

One workaround, this is to use a subquery with the select statement.

SELECT MovieID
FROM Movies
WHERE MovieID IN (SELECT MovieID FROM Movies WHERE MovieName LIKE '%Horror%')

The other approach is to create a SQL Server User-Defined Function (UDF) like this:

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.

To call the function, you need to do this:

SELECT Columns
FROM Movies
WHERE MovieID IN (SELECT CAST(Value AS int) FROM dbo.udf_Split(@ListOfMovieIDs, ','))

Displaying the Details Records Belonging to the Selected Master Records

When we put it all together this is the outcome

<%@ Page Language="VB" CodeFile="SplitUDF.aspx.vb" Inherits="MoviesByCategory" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Using a UDF Split Function</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    <h2>Choose the Movie Categories to View</h2>
    <p>
        <asp:CheckBoxList ID="CategoryList" runat="server" 
            DataSourceID="CategoriesDataSource" DataTextField="Name" 
            DataValueField="ID" RepeatDirection="Horizontal" RepeatColumns="4">
        </asp:CheckBoxList>
        
        <asp:SqlDataSource ID="CategoriesDataSource" runat="server" 
            ConnectionString="<%$ ConnectionStrings:MyDatabase %>" 
            SelectCommand="SELECT [ID], [Name] FROM [MovieCategories] ORDER BY [Name]">
        </asp:SqlDataSource>
        <asp:Button ID="RefreshMovies" runat="server" Text="Update Movie List" />
    </p>
    <p>
        <asp:GridView ID="MoviesBySelectedCategoriesGrid" runat="server" 
            CellPadding="4" DataSourceID="MoviesBySelectedCategoriesDataSource" 
            ForeColor="#333333" GridLines="None" AutoGenerateColumns="False" 
            AllowSorting="True" 
            Width="95%"
            EmptyDataText="There are no movies that belong to the categories you have selected." 
            AllowPaging="True">
            <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#E3EAEB" />
            <EmptyDataRowStyle Font-Italic="True" />
            <Columns>
                <asp:BoundField DataField="Title" HeaderText="Title" 
                    SortExpression="Title" />
                <asp:BoundField DataField="Name" HeaderText="Category" 
                    SortExpression="Name" />
                <asp:BoundField DataField="Director" HeaderText="Director" 
                    SortExpression="Director" />
                <asp:BoundField DataField="BoxOfficeTotals" DataFormatString="{0:c}" 
                    HeaderText="Box Office Totals" SortExpression="BoxOfficeTotals" />
            </Columns>
            <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#7C6F57" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
        <asp:SqlDataSource ID="MoviesBySelectedCategoriesDataSource" runat="server" 
            ConnectionString="<%$ ConnectionStrings:MyDatabase %>" 
            SelectCommand="SELECT Movies.Title, MovieCategories.Name, Movies.Director, Movies.BoxOfficeTotals 
            FROM Movies INNER JOIN MovieCategories ON Movies.CategoryID = MovieCategories.ID WHERE Movies.CategoryID 
            IN (SELECT CAST(Value as int) FROM dbo.udf_Split(@SelectedCategoryIDs, ',')) ORDER BY Name">
            <SelectParameters>
                <asp:Parameter Name="SelectedCategoryIDs" />
            </SelectParameters>
        </asp:SqlDataSource>
    </p>
</div>
    </form>
</body>
</html>

** SplitUDF.aspx.vb **

Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic

Partial Class MoviesByCategory
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'On the first page visit populate the user's default Category selections
    End Sub

    Protected Sub RefreshProducts_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles RefreshMovies.Click
        'Rebind the data to the grid
        MoviesBySelectedCategoriesGrid.DataBind()
    End Sub

    Protected Sub MoviesBySelectedCategoriesDataSource_Selecting(ByVal sender As Object, 
    ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles MoviesBySelectedCategoriesDataSource.Selecting
        'Set the @SelectedCategoryIDs parameter's value
        'First, tally up the CategoryIDs from CategoryList
        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())

        'Set the parameter value
        e.Command.Parameters("@SelectedCategoryIDs").Value = commaDelimitedString
    End Sub
End Class

Stone Coast Web Design

.NET Web Development

Using a Dynamic IN Clause With Master-Detail Records - Demo