MS SQL Server多关键词多字段搜索

为了网站能搜索，写了一个Scalar-valued Function自定义函数：

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Author,,Insus.NET>
-- Create date: <Create Date, ,2020-09-06>
-- Description:    <Description, ,多关键词多字段搜索处理成为WHERE条件语句>
-- =============================================
CREATE FUNCTION [dbo].[svf_SearchWhereCondition]
(    
    @TABLE_NAME SYSNAME,    
    @MultipleColumns NVARCHAR(MAX),
    @Delimiter CHAR(1),
    @Keyword NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result TABLE ([ID] BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,[ColumnName] NVARCHAR(MAX) NULL)
    INSERT INTO @Result([ColumnName]) SELECT [value] FROM [dbo].[tvf_String_Split](@MultipleColumns,@Delimiter) 
    
    DELETE FROM @Result WHERE NOT EXISTS (SELECT TOP 1 1 FROM INFORMATION_SCHEMA.COLUMNS AS isc 
    WHERE isc.[TABLE_SCHEMA] = PARSENAME(@TABLE_NAME,2) AND isc.[TABLE_NAME] = PARSENAME(@TABLE_NAME,1)
    AND isc.[COLUMN_NAME] = [ColumnName])    
    
    DECLARE @c NVARCHAR(MAX) = N''
    DECLARE @r INT = 1, @rs INT = 0
    SELECT @rs = MAX([ID]) FROM @Result

    WHILE @r <= @rs
    BEGIN
        IF EXISTS(SELECT TOP 1 1 FROM @Result WHERE [ID] = @r)
        BEGIN
             DECLARE @cn SYSNAME
             SELECT @cn = [ColumnName] FROM @Result WHERE [ID] = @r
             IF @r = @rs
                SET @c = @c + '['+ @cn +'] LIKE ''%''+ value +''%'''
             ELSE
                SET @c = @c + '['+ @cn +'] LIKE ''%''+ value +''%'' OR '
        END    

        SET @r = @r + 1
    END    

    DECLARE @sql NVARCHAR(MAX) = N''
    IF LEN(@c) > 0 AND RTRIM(LTRIM(@Keyword)) <> ''
        SET @sql = N' AND EXISTS(
                            SELECT TOP 1 1  
                            FROM [dbo].[tvf_String_Split](@Keyword,@Delimiter)  
                            WHERE RTRIM(LTRIM([value])) <> ''''
                            AND ('+ @c +'))'
    RETURN  @sql
END 

 

应用示例：