代码之家 › 专栏 › 技术社区 › Ivan-Mark Debono

基于列的数据类型动态构建SQL查询

sql-server-2017 sql-server sql

Ivan-Mark Debono · 技术社区 · 4 年前

史密斯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的作用是:

获取搜索筛选器并确定它们是什么数据类型
将筛选器的“数据类型与列”数据类型映射,以便 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'

它将提供视图的列及其数据类型。

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

1 回复 | 直到 4 年前

PSK 4 年前

我只是尝试使用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)