Create Procedure proc_Isvalid(@StartDate char(6) ,@StartDay smallint ,@StartTime int,@EndDate char(6),@EndDay smallint,@EndTime int,@EmployeeID char(6),@Result int Output)
with encryption
As
Declare @Code char(3)
Declare @Date char(6)
Declare @Day smallint
Declare @Time int
Declare @Shift char(2)
if exists(select s_shift from dbo.BranchSchedule where s910_01=@EmployeeID And (s_validity between @StartDate and @EndDate) And (s_day between @StartDay and @EndDay))
Begin
if exists(select s_shift from dbo.BranchSchedule where s910_01=@EmployeeID And (s_validity between @StartDate and @EndDate) And (s_day between @StartDay and @EndDay) and s_shift<>'@')
begin
Declare cur_Shift Cursor For select s210_01,validity,s_day,s_shift where s910_01=@EmployeeID And (s_validity between @StartDate and @EndDate) And (s_day between @StartDay and @EndDay) and s_shift<>'@'
open cur_Shift
Fetch cur_Shift into @Code,@Date,@Day,@Shift
While @@fetch_status = 0
Begin
if ((@StartDate=@Date)And(@StartDay=@Day))
begin
select @Time=@Time+(@StartTime-Convert(int,shift_E)) from BranchShift where s210_01=@Code And validity=@Date and shift=@Shift
end
ELse if ((@EndDate=@Date)And(@EndDay=@Day))
begin
select @Time=@Time+(@EndTime-Convert(int,shift_B)) from BranchShift where s210_01=@Code And validity=@Date and shift=@Shift
end
Else
Begin
select @Time=@Time+(Convert(int,shift_B)-Convert(int,shift_E)) from BranchShift where s210_01=@Code And validity=@Date and shift=@Shift
End
Fetch cur_Shift into @Code,@Date,@Day,@Shift
Close cur_Shift
Deallocate cur_Shift
set rowcount 0
set @Result=@Time
end
Else
begin
set @Result=1
End
End
Else
Begin
set @Result=0
End分析报错服务器: 消息 156,级别 15,状态 1,过程 proc_Isvalid,行 36
在关键字 'Else' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 proc_Isvalid,行 44
第 44 行: 'End' 附近有语法错误。自己找不出来是什么错误,各位高手能帮我看看吗?
解决方案 »
- xp_cmdshell如何执行多条语句?
- 求一存储过程!!!!!!!!!!!!!!
- 谁有通用sql server多表查询分页的存储过程急谢谢!
- !!!!!!!!!!事务加锁问题!!!!!!!!!!!!!!
- 請教表合併問題
- 散粉: 趁热儿送上刚写的一句日期相关的 SQL !
- 关于150E的数据的查方式....
- 班竹们快来呀,大力哥你真好啊,救救我呀!!!!!
- sql中出版过的database,所有的出版项都删除后,怎还不能删除database呢,怎样才能删除有过出版的database?
- 开发异构数据库用什么工具好?
- 卸载sql server2005会不会影响VS2005?
- sql2000导入数据怎样将主键一起导入
BEGIN
begin
select @Time=@Time+(@StartTime-Convert(int,shift_E)) from BranchShift where s210_01=@Code And validity=@Date and shift=@Shift
end
ELse if ((@EndDate=@Date)And(@EndDay=@Day))
begin
select @Time=@Time+(@EndTime-Convert(int,shift_B)) from BranchShift where s210_01=@Code And validity=@Date and shift=@Shift
end
END
Else
Begin
select @Time=@Time+(Convert(int,shift_B)-Convert(int,shift_E)) from BranchShift where s210_01=@Code And validity=@Date and shift=@Shift
End
While @@fetch_status = 0
Begin
if ((@StartDate=@Date)And(@StartDay=@Day))
begin
select @Time=@Time+(@StartTime-Convert(int,shift_E)) from BranchShift where s210_01=@Code And validity=@Date and shift=@Shift
end
ELse if ((@EndDate=@Date)And(@EndDay=@Day))
begin
select @Time=@Time+(@EndTime-Convert(int,shift_B)) from BranchShift where s210_01=@Code And validity=@Date and shift=@Shift
end
end
Else
Begin
select @Time=@Time+(Convert(int,shift_B)-Convert(int,shift_E)) from BranchShift where s210_01=@Code And validity=@Date and shift=@Shift
End
问题找出来了,是while后面忘记End了,太多了,绕的头晕了..
但是现在还有个问题,请各位帮忙一下Create Procedure proc_Isvalid(@StartDate char(6) ,@StartDay smallint ,@StartTime int,@EndDate char(6),@EndDay smallint,@EndTime int,@EmployeeID char(6),@Result int Output)
with encryption
As
Declare @Code char(3)
Declare @Date char(6)
Declare @Day smallint
Declare @Time int
Declare @Shift char(2)
if exists(select s_shift from dbo.BranchSchedule where s910_01=@EmployeeID And (s_validity between @StartDate and @EndDate) And (s_day between @StartDay and @EndDay))
Begin
if exists(select s_shift from dbo.BranchSchedule where s910_01=@EmployeeID And (s_validity between @StartDate and @EndDate) And (s_day between @StartDay and @EndDay) and s_shift<>'@')
begin
Declare cur_Shift Cursor For select s210_01,s_validity,s_day,s_shift from BranchSchedule where s910_01=@EmployeeID And (s_validity between @StartDate and @EndDate) And (s_day between @StartDay and @EndDay) and s_shift<>'@'
open cur_Shift
Fetch cur_Shift into @Code,@Date,@Day,@Shift
While @@fetch_status = 0
Begin
if ((@StartDate=@Date)And(@StartDay=@Day))
begin
select @Time=@Time+(@StartTime-Convert(int,shift_E)) from BranchShift where s210_01=@Code And validity=@Date and shift=@Shift
end
ELse if ((@EndDate=@Date)And(@EndDay=@Day))
begin
select @Time=@Time+(@EndTime-Convert(int,shift_B)) from BranchShift where s210_01=@Code And validity=@Date and shift=@Shift
end
Else
Begin
select @Time=@Time+(Convert(int,shift_B)-Convert(int,shift_E)) from BranchShift where s210_01=@Code And validity=@Date and shift=@Shift
End
Fetch cur_Shift into @Code,@Date,@Day,@Shift
End
Close cur_Shift
Deallocate cur_Shift
set rowcount 0
set @Result=@Time
end
Else
begin
set @Result=1
End
End
Else
Begin
set @Result=0
End--測試環境
declare @StartDate char(6)
declare @StartDay smallint
declare @StartTime int
declare @EndDate char(6)
declare @EndDay smallint
declare @EndTime int
declare @EmployeeID char(6)
declare @Result int set @StartDate='200910'
set @EndDate='200910'
set @StartDay=1
set @EndDay=9
set @StartTime=1500
set @EndTime=1300
set @EmployeeID='50968'exec dbo.proc_Isvalid @StartDate,@StartDay,@StartTime ,@EndDate ,@EndDay,@EndTime,@EmployeeID,@Result Output
print @Result这是测试数据,
如果set @EndDay=为5就会返回1,1就是表示为休假,但是如果为9的话就什么都没有返回,各位能帮我看看吗?