SQL Server include search filter colums in where clause

Question

I have a SQL Server database with a Person table containing customer data. In my web application a popup search box (jqGrid) allows users to specify the fields / filters to use in a search along with the operation and data. So the fields are like name, last visit date etc. (which map to the Person table although not all fields match the column names) and the operations are contains, starts with, ends with, greater than, etc. (each one is passed in as a two letter code like 'cn', 'bw', 'ew', 'gt'). In total there are 15 different fields that can be filtered on and 12 operations.

(There are about 50 columns in the table so I just included a few here):

CREATE TABLE [dbo].[Person]
(
    [PersonId] [int] IDENTITY(1,1) NOT NULL,
    [PersonTypeId] [int] NULL,
    [Firstname] [nvarchar](200) NULL,
    [Middlename] [nvarchar](200) NULL,
    [Lastname] [nvarchar](200) NULL,
    [Name] [nvarchar](200) NULL,
)

INSERT INTO [dbo].[Person]([PersonTypeId],[Firstname],[Middlename],[Lastname],[Name]) 
VALUES (1, 'James', 'Joseph', 'Martin', ''),
       (1, 'Jim', 'Joseph', 'Martyn', ''),
       (1, 'James', 'John', 'Martine', ''),
       (2, 'James', 'Sean', 'Martin', 'Martin & Co'),
       (2, 'John', 'Joseph', 'Martin', 'Martin & Martin')

I added a table valued parameter to the stored procedure to pass the search criteria which works well, so now in the search stored procedure I have an udt containing all the fields / operations / data entered by the user.

CREATE TYPE [dbo].[udt_Filter] AS TABLE
        (
            [Field] [nvarchar](50),
            [Op] [nvarchar](50),
            [Data] [nvarchar](50)
        )

The filters passed in via the udt will be like this:

Field   Op  Data
'Name', 'bw', 'Mart'
'LastVisit', 'gt', '01.07.2017'

Now I'm wondering what's the best way to include the filters in the search. I know I can make it work using dynamic SQL, but I'd prefer to do it using a join if possible, however I can't see any way of joining the field name filters with the data in the Person table.

ALTER PROCEDURE [dbo].[pr_GetPersons]
    (
        @Filters dbo.udt_Filter READONLY,
        @Operation nvarchar(3) -- AND or OR
    )
AS
BEGIN
    SELECT [PersonTypeId], [Firstname], [Middlename], [Lastname], [Name] 
    FROM [dbo].[Person]
    WHERE PersonTypeId IN (1, 2, 3)
        -- @Operation can I somehow join to @Filters and filter the Select result using each row in @Filters??
END

All the filter conditions are combind with either AND or OR which is passed in as @Operation to the stored procedure.

Answer 1

It's not possible to solve this dynamic filtering with a join between the Person table and the @Filters table parameter. If you go without dynamic SQL, with 15 fields and 12 operators, you'll end up with a monster where clause. I doubt the stored procedure would perform well in that case.

Instead, I suggest to go with dynamic SQL. Here's a stored procedure you can use as a starting point:

CREATE PROCEDURE [dbo].[pr_GetPersons]
    (@Filters dbo.udt_Filter READONLY)
AS
BEGIN

    DECLARE
        @SelectList nvarchar(max),
        @WhereClause nvarchar(max),
        @SqlCommand nvarchar(max)

    SET @SelectList = ISNULL(STUFF((SELECT ', ' + QUOTENAME(Field) AS [text()] FROM @Filters FOR XML PATH('')), 1, 1, ''), '*')

    SET @WhereClause = STUFF((
        SELECT 
            ' AND ' +
            -- Translate opcodes into SQL Server expressions.
            CASE Op
                WHEN 'cn' THEN QUOTENAME(Field) + ' LIKE ' + QUOTENAME('%' + Data + '%', '''')
                WHEN 'bw' THEN QUOTENAME(Field) + ' LIKE ' + QUOTENAME(Data + '%', '''')
                WHEN 'ew' THEN QUOTENAME(Field) + ' LIKE ' + QUOTENAME('%' + Data, '''')
                WHEN 'gt' THEN QUOTENAME(Field) + '>' + QUOTENAME(Data, '''')
                WHEN 'lt' THEN QUOTENAME(Field) + '<' + QUOTENAME(Data, '''')
            END AS [text()] 
        FROM 
            @Filters
        FOR XML PATH(''), type).value('.', 'nvarchar(max)'), 
        1, 5, '')

    SET @SqlCommand = 'SELECT ' + @SelectList + ' FROM dbo.Person WHERE PersonTypeId IN (1, 2, 3)' + ISNULL(NULLIF(' AND ', @WhereClause) + @WhereClause, '')

    EXEC(@SqlCommand)

END

It returns only columns that are in the filter. If you want it to return all the columns in the table, regardless of what is in the filter, set @SelectList variable to *.

All filter criterias are joined with AND operator. (In the @Fields variable there's no indication how to combine them. So I suppose AND operator is what is needed.)

The CASE expression that interprets op codes from the @Filters table to generate the where clause could be moved into its own function. QUOTENAME(Data, '''') serves to form proper strings in such cases when Data is a string and contains single quotes.

The procedure doesn't check if filter fields exist in the Person table. Special care should be taken about filter values (Data), especially for date/time and decimal types, since there is no conversion of types (all filter values are used as strings when forming the where clause) nor checking if filter value types match column types in the Person table.

Hope it helps.