具有多个可选搜索参数的 sql 搜索查询

sql search query with multiple optional search parameters

提问by vicky

I have a query in sql 2008 where a use can select multiple optional parameters like these:

我在 sql 2008 中有一个查询，其中用户可以选择多个可选参数，如下所示：

@DateFrom
@DateTo
@UserType
@Customer
@User

What's the best / most performant approach to filtering based on all of these options?

基于所有这些选项的最佳/最高效过滤方法是什么？

• separate select statements for different cases
• using coalesce
• etc.

• 针对不同情况的单独选择语句
• 使用聚结
• 等等。

回答by Shantanu Gupta

You can avoid using multiple IF ELSE condition and dynamic query by using this approach.

通过使用这种方法，您可以避免使用多个 IF ELSE 条件和动态查询。

SELECT * FROM TBL
WHERE (@Name IS NULL OR Name = @Name) 
AND (@Age IS NULL OR Age = @Age)

回答by M.Ali

you can write you WHERE Clause something like this.....

你可以写你 WHERE 子句这样的东西.....

This is a quick fix but a bad approach,,,,,

这是一个快速的解决办法，但一个不好的方法，，，，，

SELECT ......
FROM TABLE
WHERE 
     (@UserType IS NULL OR UserType = @UserType)
AND
    (@Customer IS NULL OR Customer = @Customer)
AND
    (@User IS NULL OR User = @User)
AND
    (<Similarly Other Conditions>)

The proper way of writing a query like this should be using dynamic SQL with the use of sp_executesql

编写这样的查询的正确方法应该是使用带有 sp_executesql 的动态 SQL

-- Optional Variables

Declare @UserType   VARCHAR(10) = NULL
    ,   @Customer   INT = 123
    ,   @User       INT = 123
    ,   @Sql        NVARCHAR(MAX);


-- Build SQL Dynamically 

 SET @Sql = N'  SELECT *
                FROM TABLE_Name
                WHERE 1 = 1 '
          + CASE WHEN @UserType IS NOT NULL THEN
            N' AND UserType = @UserType ' ELSE N' ' END
          +  CASE WHEN @Customer IS NOT NULL THEN
            N' AND Customer = @Customer ' ELSE N' ' END
          +  CASE WHEN @User IS NOT NULL THEN
            N' AND User = @User ' ELSE N' ' END

-- Finally Execute SQL 

 Exec sp_executesql @Sql
                , N'@UserType VARCHAR(10) , @Customer INT , @User INT'
                , @UserType
                , @Customer
                , @User;

回答by meziantou

You can use a dynamic query

您可以使用动态查询

SELECT @sql=
'SELECT DISTINCT [Test_Id], [Test].[Test_Name], [Test].[Test_Date]
    FROM [Test]
    WHERE (1 = 1)'

IF @start IS NOT NULL
    SELECT @sql = @sql + ' AND ([Test].[Test_Date] >= @start)'
IF @end IS NOT NULL
    SELECT @sql = @sql + ' AND ([Test].[Test_Date] <= @end)'

SELECT @paramlist = '@start datetime, 
    @end datetime'

EXEC sp_executesql @sql, @paramlist,
    @start, 
    @end

You can also use different queries but if you have more than 2 parameters it's very annoying code

您也可以使用不同的查询，但如果您有 2 个以上的参数，则代码非常烦人

IF(@start IS NULL)
    IF(@end IS NULL)
        SELECT ...
    ELSE
        SELECT ...
ELSE
    IF(@end IS NULL)
        SELECT ...
    ELSE
         SELECT ...