时间戳

首页 > 解决方案 > 带有多 where 语句的过程

sql - 带有多 where 语句的过程

问题描述

有下表

table TableAB(
        ColumnA int,
        ColumnB nvarchar(50)
)

我需要创建一个返回ColumnA，ColumnB并采用两个参数的过程（在现实生活中的参数计数超过 20）。所有搜索都应使用通配符“%”。

procedure has input parameters
        @ColumnA int,
        @ColumnB nvarchar(50)

我有两种方法

1.

select ColumnA,ColumnB from TableAB
where 
     ColumnA like 
     case  @ColumnA
        when @ColumnA NULL then ColumnA
        else '%' + CONVERT(varchar(10),@ColumnA)+'%'
     end
     and
     ColumnB like 
     case  @ColumnB
        when @ColumnB NULL then ColumnB
        else '%' + ColumnB +'%'
     end

2.

 DECLARE @TabWhereConditions TABLE(Id INT IDENTITY(1,1), Condition VARCHAR(MAX))
    ...
    SET @ParamDefenition = '@pColumnA int, @pColumnB nvarchar(50)'

    IF(NOT @ColumnA IS NULL)
    BEGIN
        INSERT INTO @TabWhereConditions(Condition) 
        VALUES('ColumnA like ' + '''%' + CONVERT(varchar(10),@ColumnA) + '%''') 
    END 
    IF(NOT @ColumnB IS NULL)
    BEGIN
        INSERT INTO @TabWhereConditions(Condition) 
        VALUES('ColumnA like ' + '''%' + @ColumnB + '%''')  
    END  

    DECLARE CondCursor CURSOR FOR
    SELECT Condition FROM @TabWhereConditions
    OPEN CondCursor

    SET @WhereString = ''
    FETCH NEXT FROM CondCursor INTO @WhereCondition
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @WhereString = @WhereString + @WhereCondition + ' AND '

        FETCH NEXT FROM CondCursor INTO @WhereCondition
    END

    CLOSE CondCursor
    DEALLOCATE CondCursor

    SET @WhereString =  SUBSTRING(@WhereString,1, LEN(@WhereString)-4)

    SET @SqlCommand = '
    SELECT 
      ColumnA,
      ColumnB
    FROM TableAB 
    WHERE ' + @WhereString 


    EXECUTE sp_executesql @SqlCommand, @ParamDefenition, 
        @pColumnA = @ColumnA, 
        @pColumnB = @ColumnB,

哪种方法更好？第一个或第二个，或您的建议 注意：我需要解决程序可以采用 1 到 20 个参数的情况，每次调用可以获得不同数量的参数

标签： sqlsql-server

解决方案

I believe you want to use something along these lines

select ColumnA,ColumnB from TableAB
where (@columnA is null or ColumnA like '%'+CONVERT(varchar(10),@ColumnA)+'%' and
      (@columnB is null or ColumnB like '%'+CONVERT(varchar(10),@ColumnB)+'%'

As noted in this Aaron Bertrand blog post, it is a good idea to make it dynamic T-SQL. Therefore, you may use sys.sp_executesql to run the above statement.

推荐阅读