我有以下带有四个参数的存储过程。
存储过程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 clause?
这称为包罗万象的查询，基本上是这样的：
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
您可能还想阅读这篇文章关于捕获所有查询 http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
