时间戳

首页 > 解决方案 > 根据列的数据类型动态构建 SQL 查询

sql - 根据列的数据类型动态构建 SQL 查询

问题描述

在我的应用程序中，我有一个功能，用户可以输入多个搜索字段，这些字段将用于查询数据库，例如，用户输入：

史密斯 123456 伦敦 12/01/2020

这些字段将作为表值参数（由一列作为 varchar 组成）传递给存储过程。sp 使用视图作为其数据源。例如对于上面的自定义搜索，会有一个包含以下列的视图：

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

sp需要动态构建sql查询，这个查询应该是这样的

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)

所以基本上， sp 所做的是：

  1. 使用搜索过滤器并确定它们是什么数据类型
  2. 将过滤器的数据类型映射到列的数据类型，例如，将int过滤器映射到所有int列等。

所以我从以下开始：

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'

这将为我提供视图的列及其数据类型。

但是我如何匹配数据类型（因为过滤器来自 TVP），以便我可以构建各种条件？

或者，我可以更改 TableType，使其具有 3 个唯一列（int、varchar、datetime），并且应用程序确定数据类型并将值添加到正确的列中。

标签： sqlsql-serversql-server-2017

解决方案

我只是尝试使用 while 循环构建查询并检查数据类型，如下所示。

为了便于理解，我在查询本身中添加了注释。

待办事项：

1-您需要在以下查询中添加其他数据类型。

2-您需要参数化查询并使用sp_executesql而不是执行以避免任何sql注入攻击。

--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)

推荐阅读