问个存储过程的问题

[Msconfig_001]

ALTER PROCEDURE dbo.gongzi_qry_rpt
@peoplename varchar(30),@deptname varchar(30),@moduser varchar(30),@Mouth varchar(30)
AS
if @peoplename is null
set @peoplename =''
if @deptname is null
set @deptname =''
if @mouth is null
set @mouth =''
if exists(select payday from vpay where payday = '日')
begin
select peoplename,peopleno,mouth,baoxian,deptname,jobs,workdays,payday,jbpay,overtime,weekdaytime,nightwork,leave,huoshi,jiangjin,zhusu,duofu,fading,zhuanru,
(fading+workdays)*jbpay as vjbpay,
(overtime*1.5+weekdaytime*2)*jbpay/8 as jiaban ,
(fading+workdays)*jbpay +(overtime*1.5+weekdaytime*2)*jbpay/8 as jiben,
inworkpay*(workdays+fading)/(workdays+fading+leave) as inworkpay,
((fading+workdays)*jbpay)+((overtime*1.5+weekdaytime*2)*jbpay/8)-(baoxian+huoshi+zhusu)+(inworkpay*(workdays+fading)/(workdays+fading+leave))+jiangjin-zhuanru +nightwork as shiji,
((fading+workdays)*jbpay)+(inworkpay*(workdays+fading)/(workdays+fading+leave))+jiangjin-zhuanru+duofu +((overtime*1.5+weekdaytime*2)*jbpay/8)-(baoxian+huoshi+zhusu)+nightwork as yinfa,
(baoxian+huoshi+zhusu) as koukuan 
 from vpay 
 where peoplename like '%'+ @peoplename +'%' and deptname like '%'+ @deptname +'%' and mouth like '%'+ @mouth +'%' order by id
end else
select peoplename,peopleno,mouth,baoxian,deptname,jobs,workdays,payday,jbpay,overtime,weekdaytime,nightwork,leave,huoshi,jiangjin,zhusu,duofu,fading,zhuanru,
(fading+workdays)*jbpay as vjbpay,
(overtime*1.5+weekdaytime*2)*jbpay/8  as jiaban ,
(fading+workdays)*jbpay +(overtime*1.5+weekdaytime*2)*jbpay/8 as jiben,
inworkpay*(workdays+fading)/(workdays+fading+leave) as inworkpay,
 (fading+workdays)*jbpay+(inworkpay*(workdays+fading)/(workdays+fading+leave))+jiangjin-zhuanru as shiji,
 (fading+workdays)*jbpay+(inworkpay*(workdays+fading)/(workdays+fading+leave))+jiangjin-zhuanru+duofu as yinfa,
 (baoxian+huoshi+zhusu) as koukuan 
 from vpay 
 where peoplename like '%'+ @peoplename +'%' and deptname like '%'+ @deptname +'%' and mouth like '%'+ @mouth +'%' order by id

RETURN

为什么他只执行上面一条?
我查询数据里面有payday='月'的数据的!!!

[ljhcy99]

if exists(select payday from vpay where payday = '日')
begin
select peoplename,peopleno,mouth,baoxian,deptname,jobs,workdays,payday,jbpay,overtime,weekdaytime,nightwork,leave,huoshi,jiangjin,zhusu,duofu,fading,zhuanru,
(fading+workdays)*jbpay as vjbpay,
(overtime*1.5+weekdaytime*2)*jbpay/8 as jiaban ,
(fading+workdays)*jbpay +(overtime*1.5+weekdaytime*2)*jbpay/8 as jiben,
inworkpay*(workdays+fading)/(workdays+fading+leave) as inworkpay,
((fading+workdays)*jbpay)+((overtime*1.5+weekdaytime*2)*jbpay/8)-(baoxian+huoshi+zhusu)+(inworkpay*(workdays+fading)/(workdays+fading+leave))+jiangjin-zhuanru +nightwork as shiji,
((fading+workdays)*jbpay)+(inworkpay*(workdays+fading)/(workdays+fading+leave))+jiangjin-zhuanru+duofu +((overtime*1.5+weekdaytime*2)*jbpay/8)-(baoxian+huoshi+zhusu)+nightwork as yinfa,
(baoxian+huoshi+zhusu) as koukuan
from vpay
where peoplename like '%'+ @peoplename +'%' and deptname like '%'+ @deptname +'%' and mouth like '%'+ @mouth +'%' order by id
end
if exists(select payday from vpay where payday = '月')
begin
select peoplename,peopleno,mouth,baoxian,deptname,jobs,workdays,payday,jbpay,overtime,weekdaytime,nightwork,leave,huoshi,jiangjin,zhusu,duofu,fading,zhuanru,
(fading+workdays)*jbpay as vjbpay,
(overtime*1.5+weekdaytime*2)*jbpay/8 as jiaban ,
(fading+workdays)*jbpay +(overtime*1.5+weekdaytime*2)*jbpay/8 as jiben,
inworkpay*(workdays+fading)/(workdays+fading+leave) as inworkpay,
(fading+workdays)*jbpay+(inworkpay*(workdays+fading)/(workdays+fading+leave))+jiangjin-zhuanru as shiji,
(fading+workdays)*jbpay+(inworkpay*(workdays+fading)/(workdays+fading+leave))+jiangjin-zhuanru+duofu as yinfa,
(baoxian+huoshi+zhusu) as koukuan
from vpay
where peoplename like '%'+ @peoplename +'%' and deptname like '%'+ @deptname +'%' and mouth like '%'+ @mouth +'%' order by id
end
看看

[Msconfig_001]

也是不行!!!
吖..搞不明白啊.分析是没错的!~

[allen_sz]

SQL语句没有细看，不过你这里首先有个问题是当你的语句中有对汉字进行比较，应当使用nvarchar的方式；其次你用一条可能得出记录集的语句与一个字符串比较，返回多行怎么办？比如这句

select payday from vpay where payday = '日'

最好写成

select top 1 payday from vpay where payday = N'日'

,避免我上面提到的两个问题

[yangshenghong]

ALTER PROCEDURE dbo.gongzi_qry_rpt
@peoplename varchar(30),@deptname varchar(30),@moduser varchar(30),@Mouth varchar(30)
AS
if @peoplename is null
set @peoplename =''
if @deptname is null
set @deptname =''
if @mouth is null
set @mouth =''
if exists(select payday from vpay where payday = '日')
begin
select peoplename,peopleno,mouth,baoxian,deptname,jobs,workdays,payday,jbpay,overtime,weekdaytime,nightwork,leave,huoshi,jiangjin,zhusu,duofu,fading,zhuanru,
(fading+workdays)*jbpay as vjbpay,
(overtime*1.5+weekdaytime*2)*jbpay/8 as jiaban ,
(fading+workdays)*jbpay +(overtime*1.5+weekdaytime*2)*jbpay/8 as jiben,
inworkpay*(workdays+fading)/(workdays+fading+leave) as inworkpay,
((fading+workdays)*jbpay)+((overtime*1.5+weekdaytime*2)*jbpay/8)-(baoxian+huoshi+zhusu)+(inworkpay*(workdays+fading)/(workdays+fading+leave))+jiangjin-zhuanru +nightwork as shiji,
((fading+workdays)*jbpay)+(inworkpay*(workdays+fading)/(workdays+fading+leave))+jiangjin-zhuanru+duofu +((overtime*1.5+weekdaytime*2)*jbpay/8)-(baoxian+huoshi+zhusu)+nightwork as yinfa,
(baoxian+huoshi+zhusu) as koukuan 
 from vpay 
 where peoplename like '%'+ @peoplename +'%' and deptname like '%'+ @deptname +'%' and mouth like '%'+ @mouth +'%' order by id
end 
else
begin
select peoplename,peopleno,mouth,baoxian,deptname,jobs,workdays,payday,jbpay,overtime,weekdaytime,nightwork,leave,huoshi,jiangjin,zhusu,duofu,fading,zhuanru,
(fading+workdays)*jbpay as vjbpay,
(overtime*1.5+weekdaytime*2)*jbpay/8  as jiaban ,
(fading+workdays)*jbpay +(overtime*1.5+weekdaytime*2)*jbpay/8 as jiben,
inworkpay*(workdays+fading)/(workdays+fading+leave) as inworkpay,
 (fading+workdays)*jbpay+(inworkpay*(workdays+fading)/(workdays+fading+leave))+jiangjin-zhuanru as shiji,
 (fading+workdays)*jbpay+(inworkpay*(workdays+fading)/(workdays+fading+leave))+jiangjin-zhuanru+duofu as yinfa,
 (baoxian+huoshi+zhusu) as koukuan 
 from vpay 
 where peoplename like '%'+ @peoplename +'%' and deptname like '%'+ @deptname +'%' and mouth like '%'+ @mouth +'%' order by id
end
RETURN

[ljhcy99]

那就是只满足了

if exists(select payday from vpay where payday = '日')
条件/

[Msconfig_001]

但是我里面有数据啊!~

以前都没这问题.奇怪...

[Msconfig_001]

我执行出来的结果是,不论你是月还是日,都是按照第一条SELECT执行!!
是不是第二条有错?他不执行?但是payday是月啊.为什么还是按照第一条执行?

奇怪!!!

[Msconfig_001]

[Quote=引用 1 楼 ljhcy99 的回复:]
那就是只满足了

if exists(select payday from vpay where payday = '日')
条件/
[/Quote]

里面有数据.为什么会只满足一个条件呢?

而且另一个条件也是采用第一条SELECT执行的!