SQL Server 2008 R2:准备动态 WHERE 子句 [英] SQL Server 2008 R2: Prepare Dynamic WHERE Clause
本文介绍了SQL Server 2008 R2:准备动态 WHERE 子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下带有四个参数的存储过程.
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屋!
查看全文