SQL过滤查询

本文介绍了SQL过滤查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

I have a table with a number of fields in it. I am trying to create search filter in asp 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)