在存储过程中构建动态WHERE子句

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.

我正在使用SQL Server 2008 Express，并且我有一个基于参数从表中执行SELECT的存储过程。我有nvarchar参数和int参数。

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

这是我的问题，我的where子句看起来像这样：

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

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.

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

Thanks in advance

提前致谢

3 个解决方案

#1

Try this instead:

试试这个：

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)
...

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 ...

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

#2

try something like

尝试类似的东西

AND companies_SimpleList.CityId = @CityId or @CityID = 0

#3

Here is another easy-to-read solution for SQL Server >=2008

这是另一个易于阅读的SQL Server> = 2008解决方案

CREATE PROCEDURE FindEmployee
    @Id INT = NULL,
    @SecondName NVARCHAR(MAX) = NULL
AS 
    SELECT  Employees.Id AS "Id",
            Employees.FirstName AS "FirstName",
            Employees.SecondName AS "SecondName"
    FROM Employees
    WHERE Employees.Id = COALESCE(@Id, Employees.Id)
    AND Employees.SecondName LIKE COALESCE(@SecondName, Employees.SecondName) + '%'

#1