declare @serch_condition char(20) set @serch_condition = ''if 输入姓名内容<>'' set @serch_condition = rtrim(@serch_condition) + 'name like ''%' + '输入姓名' + '%'''if 输入年龄内容<>'' set @serch_condition = rtrim(@serch_condition) + ' or age like ''%' + '输入年龄' + '%'''if 输入地址内容<>'' set @serch_condition = rtrim(@serch_condition) + ' or addr like ''%' + '输入地址' + '%'''if @serch_condition <> '' set @serch_condition = 'select * from 表 where ' + rtrim(@serch_condition) else set @serch_condition = 'select * from 表' --你要作的其他事情
mjhnet(天涯沦落人) :如果姓名为空年龄地址不为空呢? 是不是成了:select * from 表 or age like .....?
select * from table where name like '%'+@name+'%' and age like '%'+@age+'%' and addr like '%'+@addr+'%'
to: fenglinzh(枫林) exec sle_2 null,'4','2003' 查不出数据!
可以直接用下面的查询语句 select * from 表 where (name like '%'+@name+'%' or isnull(@name,'')='') and (age=17 or isnull(@age,0)=0) and (addr=@addr or isnull(@addr,'')='')
@name,@age,@addr为用户输入的数据,如果为null或''表示用户未输入
调整一下:--模糊查询的方法: select * from 表 where (name like '%'+isnull(@name,'')+'%') and (age=@age or isnull(@age,0)=0) and (addr like '%'+isnull(@addr,'')+'%')--全匹配的查询方法: select * from 表 where (name=@name or isnull(@name,'')='') and (age=@age or isnull(@age,0)=0) and (addr=@addr or isnull(@addr,'')='')
我想在存储过成里判断怎么办?
我想在存储过成里判断怎么办?
我只是举个例子,其实我的查询条件不只3个,那要写多少sql 语句啊!
set @serch_condition = ''if 输入姓名内容<>''
set @serch_condition = rtrim(@serch_condition) + 'name like ''%' + '输入姓名' + '%'''if 输入年龄内容<>''
set @serch_condition = rtrim(@serch_condition) + ' or age like ''%' + '输入年龄' + '%'''if 输入地址内容<>''
set @serch_condition = rtrim(@serch_condition) + ' or addr like ''%' + '输入地址' + '%'''if @serch_condition <> ''
set @serch_condition = 'select * from 表 where ' + rtrim(@serch_condition)
else
set @serch_condition = 'select * from 表' --你要作的其他事情
是不是成了:select * from 表 or age like .....?
exec sle_2 null,'4','2003'
查不出数据!
select * from 表
where (name like '%'+@name+'%' or isnull(@name,'')='')
and (age=17 or isnull(@age,0)=0)
and (addr=@addr or isnull(@addr,'')='')
select * from 表
where (name like '%'+isnull(@name,'')+'%')
and (age=@age or isnull(@age,0)=0)
and (addr like '%'+isnull(@addr,'')+'%')--全匹配的查询方法:
select * from 表
where (name=@name or isnull(@name,'')='')
and (age=@age or isnull(@age,0)=0)
and (addr=@addr or isnull(@addr,'')='')