我有一个表,里面有许多字段.我正在尝试在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)更多推荐
SQL过滤查询
发布评论