T-SQL procedure - filter parameter as Object/CLR/Xml/UDT

Question

Cliffs: Is there a known pattern for passing a standard "filter" type to a stored procedure to encapsulate stardate/enddate/pagesize/pagenum parameters?

Not sure the correct place for this question. I'm exploring the idea of passing a filtering object parameter to a stored procedure which encapsulates our common filtering parameters (startdate, enddate, pagenumber, pagesize, list of int's, etc). The reason for this is to reduce the amount of similar parameters and boilerplate SQL spread around our procedures. This would give us a more standard interface and starting point for each procedure right from the start. I haven't been able to find much info on the topic.

Pattern I've noticed - when first building most SP's they start with a single id parameter used in the where clause. At some point later, you may need to add parameters for date range parameters (startdate, enddate or dynamic ranges "ytd, mtd, dtd"). If the data set is large enough you also may need to introduce pagesize/pagenum for server side paging. After some time you may realize that you need results for a list of id's rather than a single id, so you add a CSV or XML parameter to envelope the IDs.

Ultimately many stored procedures end up with a lot of similar boilerplate and (hopefully) identical parameters for handling these standard filtering parameters. I'm trying to research known patterns for passing an encapsulated filter object parameter to my procedures, that ideally would be strongly typed on the C# side. This would be particularly useful when managing a group of procedures that power reports which all require the same filtering options (in addition to the report-specific query parameters).

My goal is to reduce the number of parameters required to the bare minimum needed for the WHERE clause, and create a standard mechanism for passing the generic filtering options into a procedure and using those values while inside a procedure. How could this be achieved through XML or CLR or UDT parameters?

For context of this question, I'm using SQL Server 2008 via ADO.Net from C# 2.0. Unfortunately LINQ/EF is not an option for this project at this point, and we must stick with our existing RDBMS. If there is a known pattern that requires changing technologies I would be interested in hearing about it.

Edit: Appreciate the replies so far. I've added a bounty for 50pts that I'll let run for a few more days to try to promote some more discussion. If my question isn't clear enough just leave a comment..

Answer 1

I personally think that you're overthinking or trying to reduce something that doesn't need to be reduced. You're probably better off leaving stored procedure parameters alone, or trying to create some base classes and helper functions that can append sets of parameters to a command object.

However, that being said, I'll throw a solution to your question out there and see if it fits your needs:

I suggest using TSQL user defined types. Create one or more types. Maybe one for date ranges, and one for paging and sorting. I use a similar process for passing multi-row data to stored procedures. (Some of this code might need to be tweaked a bit, as I'm just modifying some code I've already written and I haven't worked with DataTable fields in quite some time.)

Ultimately, all this does is shorten the list of parameters in the application method and matching stored procedure. The stored procedure would be responsible for extracting or joining the information in the table variable. The classes listed below do provide the ability to keep these parameters strongly typed on the .NET application side.

if not exists (select * from INFORMATION_SCHEMA.DOMAINS where DOMAIN_SCHEMA = 'dbo' and DOMAIN_NAME = 'DateRange' and DATA_TYPE = 'table type')
begin

    create type dbo.DateRange as table 
    (
        StartDate datetime2 null
        ,EndDate datetime2 null
    )

end
go


if not exists (select * from INFORMATION_SCHEMA.DOMAINS where DOMAIN_SCHEMA = 'dbo' and DOMAIN_NAME = 'Paging' and DATA_TYPE = 'table type')
begin

    create type dbo.Paging as table 
    (
        PageNumber int null
        ,PageSize int null
        ,SortField sysname null
        ,SortDirection varchar(4) null
    )

end
go

The SQL user defined types can be represented as strongly typed objects in a .NET application. Start with a base class:

    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Runtime.Serialization


    Namespace SqlTypes

        <Serializable()> _
        <System.ComponentModel.DesignerCategory("Code")> _
        Public MustInherit Class SqlTableTypeBase
            Inherits DataTable

            Public Sub New()

                MyBase.New()
                Initialize()

            End Sub


            Public Sub New(ByVal tableName As String)

                MyBase.New(tableName)
                Initialize()

            End Sub


            Public Sub New(ByVal tableName As String, ByVal tableNamespace As String)

                MyBase.New(tableName, tableNamespace)
                Initialize()

            End Sub


            Protected Sub New(ByVal info As SerializationInfo, ByVal context As StreamingContext)

                MyBase.New(info, context)

            End Sub


            ''' <summary>
            ''' Implement this method to create the columns in the data table to match the SQL server user defined table type
            ''' </summary>
            ''' <remarks></remarks>
            Protected MustOverride Sub Initialize()


            Public Function CreateParameter(parameterName As String) As SqlParameter

                Dim p As New SqlParameter(parameterName, SqlDbType.Structured)
                p.Value = Me

                Return p

            End Function

        End Class

    End Namespace

Create an implementation for the SQL types:

Imports System
Imports System.Data
Imports System.Runtime.Serialization


Namespace SqlTypes

    <Serializable()> _
    <System.ComponentModel.DesignerCategory("Code")> _
    Public Class DateRange
        Inherits SqlTableTypeBase

        Public Sub New()

            MyBase.New()

        End Sub


        Public Sub New(ByVal tableName As String)

            MyBase.New(tableName)

        End Sub


        Public Sub New(ByVal tableName As String, ByVal tableNamespace As String)

            MyBase.New(tableName, tableNamespace)

        End Sub


        Protected Sub New(ByVal info As SerializationInfo, ByVal context As StreamingContext)

            MyBase.New(info, context)

        End Sub


        'TODO: throw some more overloaded constructors in here...

        Public Sub New(startDate As DateTime?, endDate As DateTime?)

            MyBase.New()

            Me.StartDate = startDate
            Me.EndDate = endDate

        End Sub


        Public Property StartDate As DateTime?
            Get
                Return CType(Me.Rows(0)(0), DateTime?)
            End Get
            Set(value As DateTime?)
                Me.Rows(0)(0) = value
            End Set
        End Property


        Public Property EndDate As DateTime?
            Get
                Return CType(Me.Rows(0)(1), DateTime?)
            End Get
            Set(value As DateTime?)
                Me.Rows(0)(1) = value
            End Set
        End Property


        Protected Overrides Sub Initialize()

            Me.Columns.Add(New DataColumn("StartDate", GetType(DateTime?)))
            Me.Columns.Add(New DataColumn("EndDate", GetType(DateTime?)))

            Me.Rows.Add({Nothing, Nothing})

        End Sub

    End Class

End Namespace

And:

Imports System
Imports System.Data
Imports System.Runtime.Serialization


Namespace SqlTypes

    <Serializable()> _
    <System.ComponentModel.DesignerCategory("Code")> _
    Public Class Paging
        Inherits SqlTableTypeBase

        Public Sub New()

            MyBase.New()

        End Sub


        Public Sub New(ByVal tableName As String)

            MyBase.New(tableName)

        End Sub


        Public Sub New(ByVal tableName As String, ByVal tableNamespace As String)

            MyBase.New(tableName, tableNamespace)

        End Sub


        Protected Sub New(ByVal info As SerializationInfo, ByVal context As StreamingContext)

            MyBase.New(info, context)

        End Sub


        'TODO: throw some more overloaded constructors in here...


        Public Sub New(pageNumber As Integer?, pageSize As Integer?)

            MyBase.New()

            Me.PageNumber = pageNumber
            Me.PageSize = pageSize

        End Sub


        Public Sub New(sortField As String, sortDirection As String)

            MyBase.New()

            Me.SortField = sortField
            Me.SortDirection = sortDirection

        End Sub


        Public Sub New(pageNumber As Integer?, pageSize As Integer?, sortField As String, sortDirection As String)

            Me.New(pageNumber, pageSize)

            Me.SortField = sortField
            Me.SortDirection = sortDirection

        End Sub


        Public Property PageNumber As Integer?
            Get
                Return CType(Me.Rows(0)(0), Integer?)
            End Get
            Set(value As Integer?)
                Me.Rows(0)(0) = value
            End Set
        End Property


        Public Property PageSize As Integer?
            Get
                Return CType(Me.Rows(0)(1), Integer?)
            End Get
            Set(value As Integer?)
                Me.Rows(0)(1) = value
            End Set
        End Property


        Public Property SortField As String
            Get
                Return CType(Me.Rows(0)(2), String)
            End Get
            Set(value As String)
                Me.Rows(0)(2) = value
            End Set
        End Property


        Public Property SortDirection As String
            Get
                Return CType(Me.Rows(0)(3), String)
            End Get
            Set(value As String)
                Me.Rows(0)(3) = value
            End Set
        End Property


        Protected Overrides Sub Initialize()

            Me.Columns.Add(New DataColumn("PageNumber", GetType(Integer?)))
            Me.Columns.Add(New DataColumn("PageSize", GetType(Integer?)))
            Me.Columns.Add(New DataColumn("SortField", GetType(String)))
            Me.Columns.Add(New DataColumn("SortDirection", GetType(String)))

            Me.Rows.Add({Nothing, Nothing, Nothing, Nothing})

        End Sub

    End Class

End Namespace

Instantiate the objects and set the values in the constructor, then simply get the parameter from the object, and append it to the stored procedure command object's parameter collection.

cmd.Parameters.Add(New DateRange(startDate, endDate).CreateParameter("DateRangeParams"))
cmd.Parameters.Add(New Paging(pageNumber, pageSize).CreateParameter("PagingParams"))

EDIT Since this answer revolves around the strong typing, I thought I should add an example of strong typing in the method signature:

'method signature with UDTs
Public Function GetMyReport(customParam1 as Integer, timeFrame as DateRange, pages as Paging) as IDataReader

'method signature without UDTs
Public Function GetMyReport(customParam1 as Integer, startDate as DateTime, endDate as DateTime, pageNumber as Integer, pageSize as Integer)

Answer 2

We also faced this problem. Solved by creating a user defined table type on the Programmability/Type section on the database.

user defined table types SQL Server 2008 R2

This table is used across all the appl when calling different stored procedures and functions. We fill in this table programmatically on the appl client side (vb.net 2010) and then pass it as parameter. On the stored procedure we just read the table and do what ever we need to do, filtering, processing, etc. Hope this helps.