Loop through SQL Table valued parameter

Question

I'm trying to achieve a advanced search functionality for my application in which i have a SQL Table Valued Parameter in the following structure,

ColumnName   Operator       Keyword
------------------------------------
Name          StartsWith     Ram
City          Equals         Chennai

My SQL table,

Name         City      CreatedDate
-----------------------------------
Ram          Chennai   10/10/2014
Ramachan     Kovai     02/03/2015

How can i loop thorough this TVP so that i can build the WHERE clause and can append it to the SELECT query which is faster since i have some 10 rows of search values(criteria).

The filters are associated with AND operator.

List of operators used:

1. Equals
2. Not equals
3. Starts with
4. Ends with
5. From(Date)
6. To(Date)

Answer 1

You can create a dynamic filtered expression like below and use it in your SQL. You need to be very careful when adding editing filters in your TVP and verifying it against respective datatypes as well

Create Type and Base Table with Data

/*
CREATE TYPE FilterTVP AS TABLE
(
ColumnName VARCHAR(30), Operator VARCHAR(30), Keyword VARCHAR(30)
);
GO

CREATE TABLE myTable
(
    Name VARCHAR(50),
    City VARCHAR(50),
    CreatedDate DATE
)
INSERT INTO myTable VALUES('Ram','Chennai','10/10/2014'),('Ramachan','Kovai','02/03/2015')

*/

Query

DECLARE @Param FilterTVP
INSERT INTO @Param VALUES('Name','StartsWith','Ram'),('City','Equals','Chennai'),('CreatedDate','From','2014-05-05')
DECLARE @FilterExp NVARCHAR(MAX) 
SELECT @FilterExp = 
(SELECT
    ' AND ' + QUOTENAME(ColumnName,'[') + ' ' + 
    CASE Operator 
        WHEN 'Equals'
        THEN '='
        WHEN 'Not equals'
        THEN '<>'
        WHEN 'StartsWith'
        THEN 'LIKE'
        WHEN 'Endswith'
        THEN 'LIKE'
        WHEN 'From'
        THEN '>='
        WHEN 'To'
        THEN '<='

    END + ' ' +
    CASE
        WHEN Operator = 'Startswith' THEN  QUOTENAME(Keyword  + '%','''') 
        WHEN Operator = 'Endswith' THEN QUOTENAME('%' + Keyword  ,'''') 
        ELSE QUOTENAME(Keyword,'''')
    END
FROM @Param
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')



SET @FilterExp = 'SELECT * FROM  myTable WHERE 1=1 ' + ISNULL(@FilterExp,'')

PRINT @FilterExp
EXEC sp_executeSQL @FilterExp

Output

SQL Fiddle

Name    City    CreatedDate
--------------------------
Ram Chennai 2014-10-10