可选的where子句 [英] optional where clause
本文介绍了可选的where子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我写了这个存储过程给用户过滤select的结果,但它根本不起作用。我有一个带有四个文本框的表单,用户可以组合填充文本框(用户无法填充所有文本框或其中一个)所以问题是什么?
CREATE PROCEDURE sp_search
@minprice bigint = null,
@maxprice bigint = null,
@minarea int = null,
@maxarea int = null,
@location nvarchar(50)= null,
@kind nvarchar(50)= null
AS
SELECT * FROM Landtbl
WHERE((@minprice is null和@maxprice is null)
或(@minprice和@maxprice之间的[price]))
和
((@minarea为null,@ maxarea为null)
或( @minarea和@maxarea之间的[区域]))
和((@location为null)或([location] = @ location))
和((@kind为null)或([kind] = @ kind))
解决方案
您的
WHERE中似乎有错误
声明。您有@maxpriceis null
。它应该是@maxprice is null
。你已经省略了maxprice和之间的空格是
。
I wrote this stored procedure to user filter the result of select but It wont work at all. I have a form with four textboxes and user can combinatorial fill the textboxes(user has not to fill all textboxes or one of them)so what is the problem?
CREATE PROCEDURE sp_search @minprice bigint=null, @maxprice bigint=null, @minarea int=null, @maxarea int=null, @location nvarchar(50)=null, @kind nvarchar(50)=null AS SELECT * FROM Landtbl WHERE ((@minprice is null and @maxpriceis null) or ([price] between @minprice and @maxprice)) and ((@minarea is null and @maxarea is null) or ([area] between @minarea and @maxarea)) and ((@location is null)or([location]=@location)) and ((@kind is null) or ([kind]=@kind))
解决方案
You appear to have have an error in your
WHERE
statement. You have@maxpriceis null
. It should read@maxprice is null
.You have omitted the space between maxprice andis
.
这篇关于可选的where子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文