动态生成SQL语句, string sql ="select * from ZX where 1=1 "; if (xm_area.SelectedItem.Value != "") s += " and XM_Area = '" + xm_area.SelectedItem.Value.ToString() + "'"; ……
select * from ZS_Store where XM_Area like %value1% and XM_Year like %value2%.....我就是用like连的,比如说用户XM_Area没选择的话,那XM_Area like %value1%就等于没写。 就不用写那么多判断语句了
Select ID from AA where ID = isnull(@parmID,id) ===弯弯的月亮小小的船,小小的船,两头尖,我在小小的船里坐,只看见闪闪 的星星蓝蓝的天. ===本贴子以“现状”提供且没有任何担保,同时也没有授予任何权利 ===我的blog:http://loulanlouzhu.blogone.net
function getData(XM_Area as ...,XM_Year as ...,XM_Type as ...,HZ_Flag as ...,Key as ...) as ... dim sql as string="select * from ZS_Store where 1=1" if XM_Area<>"" then sql+=" and XM_Area" & XM_Area end if ''其他参数同 end function
where 1=1是设定一个永远成立的条件,再来拼接and 语句;
写在存储过程中。 你改一改。 create proc pupdrec @id int, @name varchar(80), @money varchar(80), @type varchar(6) as declare @sentence varchar(700) set @sentence = 'update mymoney set ' if @name <> '' set @sentence = @sentence + 'vcr_name='+''''+@name+'''' if @money<>'0' set @sentence = @sentence + ',mny_money='+@money if @type <>'' set @sentence = @sentence + ',vcr_type='+''''+@type+''''set @sentence = @sentence + ' where int_id='+cast(@id as varchar(20))execute(@sentence)
string sql ="select * from ZX where 1=1 ";
if (xm_area.SelectedItem.Value != "")
s += " and XM_Area = '" + xm_area.SelectedItem.Value.ToString() + "'";
……
就不用写那么多判断语句了
对阿
先判断
string strSQL="select * from table";
if(1不为空)
{
strSQL=strSQL+"1条件";
}
if......最后拼在一起得SQL语句就可以了
可以详细写一下上面的语句吗?
if(XM_Area.SelectedItem.Value != null)
strSql += "XM_Area = '"+ XM_Area.SelectedItem.Value +"'";
if(XM_Year.SelectedItem.Value != null)
strSql += " And XM_Year = '"+ XM_Year.SelectedItem.Value +"'";
.....
strSql += " Order By ID Desc";
首先准备一个Select语句。
然后利用循环判断来完成Where字句的拼接。
最后根据Where的情况连接两个子句构成一个查询语句。(需要注意的是在添加最后一个And 的时候后面不能在加AND了!)
===弯弯的月亮小小的船,小小的船,两头尖,我在小小的船里坐,只看见闪闪
的星星蓝蓝的天.
===本贴子以“现状”提供且没有任何担保,同时也没有授予任何权利
===我的blog:http://loulanlouzhu.blogone.net
dim sql as string="select * from ZS_Store where 1=1"
if XM_Area<>"" then
sql+=" and XM_Area" & XM_Area
end if
''其他参数同
end function
你改一改。
create proc pupdrec
@id int,
@name varchar(80),
@money varchar(80),
@type varchar(6)
as
declare @sentence varchar(700)
set @sentence = 'update mymoney set '
if @name <> ''
set @sentence = @sentence + 'vcr_name='+''''+@name+''''
if @money<>'0'
set @sentence = @sentence + ',mny_money='+@money
if @type <>''
set @sentence = @sentence + ',vcr_type='+''''+@type+''''set @sentence = @sentence + ' where int_id='+cast(@id as varchar(20))execute(@sentence)