SQL过滤查询 [英] SQL Filter Query

问题描述

我有一个表，里面有许多字段.我正在尝试在asp.net中创建搜索过滤器，以便用户可以按一个或多个字段进行搜索.所以基本上我想创建一个包含4个参数的存储过程，如果它不为null，它将把参数附加到WHERE子句中.

I have a table with a number of fields in it. I am trying to create search filter in asp.net so the user can search by one or a combination of fields. So basically I want to create a single stored procedure that takes in 4 params and it will append the param to the WHERE clause if its not null...

TableExample有4列，即Col1 Col2 Col3 Col4

TableExample has 4 columns, Col1 Col2 Col3 Col4

我希望有一种方法可以用一个存储过程来完成此操作，而不必为每种可能的组合创建一个.

I am hoping there is way to do this with a single stored procedure instead of having to create one for each possible combination.

我正在尝试类似的方法，虽然这是不正确的，但到目前为止，它已达到目的.

I was trying something like this, which isn't correct, but its what ive got thus far.

谢谢！

CREATE PROCEDURE [dbo].[Search]
    @Col1 int,
    @Col2 int,
    @Col3 int,
    @Col4 int
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT *

FROM
    [dbo].[TestTable]
WHERE
    1=1
    CASE        
        WHEN @Col1 IN NOT NULL
        THEN AND [Col1] = @Col1

        WHEN @Col2 IN NOT NULL
        THEN AND [Col2] = @Col2

        WHEN @Col3 IN NOT NULL
        THEN AND [Col3] = @Col3

        WHEN @Col4 IN NOT NULL
        THEN AND [Col4] = @Col4
    END

推荐答案

非常感谢大家的答复.但是，我做了一些不同的事情.我希望它可以帮助某人！这是我的处理方法:

I thank you all for your replies. However, I did it a little bit differently. I hope it helps someone out! Here's how I went about it:

CREATE PROCEDURE [dbo].[TestTable_Search]
    @Col1 int,
    @Col2 uniqueidentifier,
    @Col3 datetime,
    @Col4 datetime
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT *

FROM
    [dbo].[TestTable]
WHERE
    [Col1] = COALESCE(@Col1, Col1) AND
    [Col2] = COALESCE(@Col2, Col2) AND
    [Col3] >= COALESCE(@Col3 + "00:00:00", Col3) AND
    [Col4] <= COALESCE(@Col4 + "23:59:59", Col4)

这篇关于SQL过滤查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！