SQL Server 2008 R2：准备动态WHERE子句

小编典典

SQL Server 2008 R2：准备动态WHERE子句

sql

我有以下带有四个参数的存储过程。

存储过程spTest：

CREATE PROCEDURE spTest
    @Name varchar(20) = '',
    @Address varchar(100) = '',
    @City varchar(50) = '',
    @Pin varchar(50) = ''
AS
    DECLARE @DynamicWhere varchar(max)
    DECLARE @Query varchar(max)

    /* Here I want to prepare a dynamic where clause for all possibilities */
    SET @Query = 'SELECT * FROM Test_Table '+ @DynamicWhere +'';

    EXECUTE(@Query);
GO

好吧，我正在这样准备：

IF @Name = '' AND @Address = '' AND @City = '' AND @Pin = '' 
BEGIN
     SET @DynamicWhere = '';
END
ELSE IF @Name != '' AND @Address = '' AND @City = '' AND @Pin = '' 
BEGIN
     SET @DynamicWhere = 'Name ='''+@Name+'''';
END
ELSE IF @Name != '' AND @Address != '' AND @City = '' AND @Pin = '' 
BEGIN
     SET @DynamicWhere = 'Name ='''+@Name+''' AND Address ='''+@Address+'''';
END
......
......
Many possibilities

这是一个正确的方式或有没有更好的方式来准备动态WHERE条款？

阅读 176

2021-04-07

共1个答案

小编典典

这就是所谓的全部查询，它基本上是这样的：

CREATE PROCEDURE spTest
    @Name varchar(20) = '',
    @Address varchar(100) = '',
    @City varchar(50) = '',
    @Pin varchar(50) = ''
AS
    SELECT * 
    FROM Test_Table 
    WHERE (@Name = '' OR Name = @Name)
    AND (@Address = '' OR Address = @Address)
    AND (@City = '' OR City = @City)
    AND (@Pin = '' OR Pin = @Pin);
GO

2021-04-07