Build an SQL query dynamically based on columns' datatype

Question

In my app, i have a feature where the user can enter multiple search fields and these will be used to query the db, for example, the user enters:

smith 123456 london 12/01/2020

These fields will be passed to a stored procedure as a table-valued parameter (consisting of one column as varchar). The sp uses a view as its datasource. For example for the above custom search, there will be a view with the following columns:

number, int
firstname, varchar
lastname, varchar
dob, datetime
address, varchar

The sp needs to build the sql query dynamically and this query should look like

select * from customersview
where 'smith' in (firstname, lastname, address)
and 123456 in (number)
and 'london' in (firstname, lastname, address)
and '12/01/2029' in (dob)

So basically, what the sp does is:

1. Take the search filters and determine what datatype they are
2. Map the filters' datatype with columns' datatype, so that, for example, an int filter is mapped to all int columns, etc.

So I started off with the following:

select COLUMN_NAME, DATA_TYPE
from INFORMATION_SCHEMA.VIEWS v
join INFORMATION_SCHEMA.COLUMNS c on c.TABLE_SCHEMA = v.TABLE_SCHEMA
and c.TABLE_NAME = v.TABLE_NAME
where c.TABLE_NAME = 'customersview'

which will give me the view's columns and their datatype.

But how can I match the data types (because the filters come in a TVP) so that I can build the various conditions?

Alternatively, I can change the TableType so that it has 3 unique columns (int, varchar, datetime) and the app determines the data type and adds the value in the correct column.

Answer 1

I just tried to build the query using a while loop and checking the datatype as following.

I have added comments in the query itself for easy understanding.

TODO:

1- You need to add other datatypes the below query.

2- You need to parameterized the query and use sp_executesql instead of execute to avoid any sql injection attack.

--Table to Store search inputs, which will be your table type parameter.
DECLARE @v TABLE (searchString VARCHAR(100))
--Sample Inputs
INSERT INTO @v
SELECT *
FROM (
    VALUES ('smith')
        ,('1234')
        ,('london')
        ,('12/01/2020')
    ) t(v)

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    DROP TABLE #Temp
--Create a temporary table to loop the serach inputs
SELECT *
    ,0 AS IsProcessed
INTO #Temp
FROM @v

DECLARE @query NVARCHAR(max) = 'SELECT * FROM customersview WHERE 1 = 1 '
DECLARE @searchString VARCHAR(100)
--Loop through each search input
WHILE (
        SELECT Count(*)
        FROM #Temp
        ) > 0
BEGIN
    SELECT TOP 1 @searchString = searchString
    FROM #Temp

    SELECT @searchString


    --Check if input is int/bigint type
    IF (ISNUMERIC(@searchString) = 1)
    BEGIN


        SET @query = @query + 'AND ' + @searchString + ' IN (' + Stuff((
                    SELECT DISTINCT ', ' + Quotename(COLUMN_NAME)
                    FROM (
                        SELECT COLUMN_NAME
                            ,DATA_TYPE
                        FROM INFORMATION_SCHEMA.VIEWS v
                        JOIN INFORMATION_SCHEMA.COLUMNS c ON c.TABLE_SCHEMA = v.TABLE_SCHEMA
                            AND c.TABLE_NAME = v.TABLE_NAME
                        WHERE c.TABLE_NAME = 'customersview'
                            AND DATA_TYPE IN ('int', 'bigint')
                        ) t
                    FOR XML path('')
                        ,type
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + ')'
    END
    --Check if input is date type
    ELSE IF (ISDATE(@searchString) = 1)
    BEGIN

        SET @query = @query + ' AND ''' + @searchString + ''' IN (' + Stuff((
                    SELECT DISTINCT ', ' + Quotename(COLUMN_NAME)
                    FROM (
                        SELECT COLUMN_NAME
                            ,DATA_TYPE
                        FROM INFORMATION_SCHEMA.VIEWS v
                        JOIN INFORMATION_SCHEMA.COLUMNS c ON c.TABLE_SCHEMA = v.TABLE_SCHEMA
                            AND c.TABLE_NAME = v.TABLE_NAME
                        WHERE c.TABLE_NAME = 'customersview'
                            AND DATA_TYPE IN ('date', 'datetime')
                        ) t
                    FOR XML path('')
                        ,type
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + ')'
    END
    ELSE
    BEGIN
    --Check if input is VARCHAR/NVARCHAR type
        SET @query = @query + ' AND ''' + @searchString + ''' IN (' + Stuff((
                    SELECT DISTINCT ', ' + Quotename(COLUMN_NAME)
                    FROM (
                        SELECT COLUMN_NAME
                            ,DATA_TYPE
                        FROM INFORMATION_SCHEMA.VIEWS v
                        JOIN INFORMATION_SCHEMA.COLUMNS c ON c.TABLE_SCHEMA = v.TABLE_SCHEMA
                            AND c.TABLE_NAME = v.TABLE_NAME
                        WHERE c.TABLE_NAME = 'customersview'
                            AND DATA_TYPE IN ('VARCHAR', 'NVARCHAR')
                        ) t
                    FOR XML path('')
                        ,type
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + ')'
    END

    DELETE #Temp
    WHERE searchString = @searchString
END

SELECT @query
--Execute the query
--EXEC(@Query)