create proc mzfj_fpsearch --查找病人发票号4个CHECKBOX,3个RADIOBOTTONS 2002/12/10 CCEO
@flag int,
@flags int,
@ks varchar(20),
@fpxh varchar(20),
@time0 datetime,
@time1 datetime
as
if @flag =0
begin
select select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where ks=@ks
return
end
if @flag =1
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where fpxh=@fpxh
return
end
if @flag =2
begin
if @flags=0
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where yxbz='1'
return
end
if @flags=2
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where fpcdbz='1'
return
end
if @flags=3
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where yxbz='0'
return
end
end
if @flag =3
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where dyrq between @time0 and @time1
return
endgo
@flag int,
@flags int,
@ks varchar(20),
@fpxh varchar(20),
@time0 datetime,
@time1 datetime
as
if @flag =0
begin
select select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where ks=@ks
return
end
if @flag =1
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where fpxh=@fpxh
return
end
if @flag =2
begin
if @flags=0
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where yxbz='1'
return
end
if @flags=2
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where fpcdbz='1'
return
end
if @flags=3
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where yxbz='0'
return
end
end
if @flag =3
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where dyrq between @time0 and @time1
return
endgo
解决方案 »
- 怎样把下面需要的字符截取出来?先感谢了!
- 大家帮忙看看这个写的有没有问题!!!!!!!!!!!!!!!
- js_szy 同学进来一下,我的分数给错了~~~~~
- 求一条Sql语句 查询两个表
- sql server 中如何实现行级触发器(SQL Server中多行数据更新的触发器)
- 标识了主键后,如果删除了某列的主键为100,想insert一条主键为100的数据进去怎么办?
- 我遇到一个在SQL2005中的问题:"事务中,不能存在多个使用此游标类型的记录集.......................",SQL2000是好的!
- 查询语句
- 在留言板中如何得到留言人所在机子的IP地址
- 高手请帮忙:sql排序 (按表中的一个字段的内容)
- 多人同时访问数据库问题一问
- 如何得到一个表中的主/外键信息?
或
把CASE放到WHERE后面试试。
@flag int,
@flags int,
@ks varchar(20),
@fpxh varchar(20),
@time0 datetime,
@time1 datetime
as
if @flag =0
begin
select select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where ks=@ks
return
end
if @flag =1
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where fpxh=@fpxh
return
end
if @flag =2
begin
if @flags=0
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where yxbz='1'
return
end
if @flags=1
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where fpcdbz='1'
return
end
if @flags=2
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where yxbz='0'
return
end
end
if @flag =3
begin
select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where dyrq between @time0 and @time1
return
endgo
if
begin
。
end
else
begin
......
end
@flag int,
@flags int,
@ks varchar(20),
@fpxh varchar(20),
@time0 datetime,
@time1 datetime
as
select select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb where (@flag =0 and ks=@ks)
or (@flag =1 and fpxh=@fpxh)
or (@flag =2 and @flags=0 and yxbz='1')
or (@flag =2 and @flags=1 and fpcdbz='1')
or (@flag =2 and @flags=2 and yxbz='0')
or (@flag =3 and dyrq between @time0 and @time1)go
你的情况可以写成下面逻辑表达式的形式,
结果是对是错你执行看看!create proc mzfj_fpsearch --查找病人发票号4个CHECKBOX,3个RADIOBOTTONS 2002/12/10 WOLF
@flag int,
@flags int,
@ks varchar(20),
@fpxh varchar(20),
@time0 datetime,
@time1 datetime
asselect fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb
where ((ks=@ks and @flag=0) or @flag<>0)
and ((fpxh=@fpxh and @flag=1) or @flag<>1)
and ((yxbz='1' and @flag=2 and @flags=0) or (@flag<>2 and @flags<>0))
and ((fpcdbz='1' and @flag=2 and @flags=1) or (@flag<>2 and @flags<>1))
and ((yxbz='0' and @flag=2 and @flags=2) or (@flag<>2 and @flags<>2))
and ((dyrq between @time0 and @time1 and @flag=3) or @flag<>3)
你的逻辑表达让我看了半天,如果一定要这么表达,应该是:select fpxh as 发票号,brxm as 姓名,dyrq as 结算时间,yxbz as 有效标志,fpcdbz as 重打标志,ssje as 金额 from his_mzfpb
where ((ks=@ks and @flag=0) or @flag<>0)
and ((fpxh=@fpxh and @flag=1) or @flag<>1)
and ((yxbz='1' and @flag=2 and @flags=0) or (@flag<>2 or @flags<>0))
and ((fpcdbz='1' and @flag=2 and @flags=1) or (@flag<>2 or @flags<>1))
and ((yxbz='0' and @flag=2 and @flags=2) or (@flag<>2 or @flags<>2))
and ((dyrq between @time0 and @time1 and @flag=3) or @flag<>3)
如果这样写 @flag<>2 or @flags<>0
@flag=2 时,@flag<>2都为true,三个条件都失效了,区分不了@flags值
中间是or 怎么会“三个条件都失效了”,用and才会的“两个条件都失效”反正:
not (@flag=2 and @flags=0) == not(@flag=2) or not (@flags=0) ==(@flag<>2) or (@flags<>0)
@flag=0
@flags=0
((yxbz='1' and @flag=2 and @flags=0) or (@flag<>2 and @flags<>0))=false
((yxbz='1' and @flag=2 and @flags=0) or (@flag<>2 or @flags<>0))=true
的情况。但是如果按照你的修改,注意在@flag=2 @flags=?的情况下,
(@flag<>2 or @flags<>0)) = true 推出
(********** or (@flag<>2 or @flags<>?))成为永真式!
是不是三个筛选条件都不起作用了!
不过我应该考虑到你说的这种情况,将表达式在写的严密一点的。
谢谢,:)
“但是如果按照你的修改,注意在@flag<>2 @flags=?的情况下,”