在存储过程中构建动态 WHERE 子句 [英] Building dynamic WHERE clause in stored procedure

问题描述

我正在使用 SQL Server 2008 Express，并且我有一个存储过程，它根据参数从表中执行 SELECT.我有 nvarchar 参数和 int 参数.

I'm using SQL Server 2008 Express, and I have a stored procedure that do a SELECT from table, based on parameters. I have nvarchar parameters and int parameters.

这是我的问题，我的 where 子句如下所示:

Here is my problem, my where clause looks like this:

WHERE [companies_SimpleList].[Description] Like @What 
    AND companies_SimpleList.Keywords Like @Keywords
    AND companies_SimpleList.FullAdress Like @Where
    AND companies_SimpleList.ActivityId = @ActivityId
    AND companies_SimpleList.DepartementId = @DepartementId
    AND companies_SimpleList.CityId = @CityId

此参数是我的 ASP.NET MVC 3 应用程序的用户设置的过滤器值，int 参数可能未设置，因此它们的值为 0.这是我的问题，例如，存储过程将搜索 0 作为 CityId 的项目，为此，它返回错误的结果.所以，能够有一个动态的 where 子句，基于 int 参数的值是否大于 0 会很好.

This parameters are the filter values set by the user of my ASP.NET MVC 3 application, and the int parameters may not be set, so their value will be 0. This is my problem, the stored procedure will search for items who have 0 as CityId for example, and for this, it return a wrong result. So it will be nice, to be able to have a dynamic where clause, based on if the value of int parameter is grater than 0, or not.

提前致谢

推荐答案

试试这个:

WHERE 1 = 1
AND (@what     IS NULL OR [companies_SimpleList].[Description] Like @What )
AND (@keywords IS NULL OR companies_SimpleList.Keywords        Like @Keywords)
AND (@where    IS NULL OR companies_SimpleList.FullAdress      Like @Where)
...

如果任何参数@what、@where 被发送到带有NULL 值的存储过程，则该条件将被忽略.您可以使用 0 而不是 null 作为测试值，然后它将类似于 @what = 0 OR ...

If any of the parameters @what, @where is sent to the stored procedure with NULL value then the condition will be ignored. You can use 0 instead of null as a test value then it will be something like @what = 0 OR ...

这篇关于在存储过程中构建动态 WHERE 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！