分类导航

程序问答发布时间：2022-06-02 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了SQL Server 2008 R2：准备动态WHERE子句，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

如何解决SQL Server 2008 R2：准备动态WHERE子句？

开发过程中遇到SQL Server 2008 R2：准备动态WHERE子句的问题如何解决？下面主要结合日常开发的经验，给出你关于SQL Server 2008 R2：准备动态WHERE子句的解决方法建议，希望对你解决SQL Server 2008 R2：准备动态WHERE子句有所启发或帮助；

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

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

解决方法

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

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