SQL Server 2008 R2:准备动态 WHERE 子句 [英] SQL Server 2008 R2: Prepare Dynamic WHERE Clause

问题描述

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

I have the following stored procedure with four parameters.

存储过程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 

这是正确的方法OR是否有更好的方法来准备动态WHERE子句?

Is this a right way OR is there any better way to prepare the dynamic WHERE clause?

推荐答案

它被称为全能查询，基本上是这样的:

It's called catch-all queries and it basically goes like this:

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

您可能还想阅读这篇关于捕获所有查询

这篇关于SQL Server 2008 R2:准备动态 WHERE 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！