帮忙解决语法错误
CREATE PROCEDURE rw_period_ini @year int,@errmsg varchar(500) output AS
Declare @iperiod int,@Fyear int, @a int,@i int
Begin
If exists(select period from period where period=@iperiod)
Set @errmsg='该期间已经存在，不能重复添加'
Else
Begin
begin
If exists(select period from period where period='0') /*期间表为空*/
Select svalue from sysconfig where @Fyear =svalue /*取系统设置表中的系统启用日期作为当前日期*/
Else
Select @Fyear=max(@iperiod) from period
end
Begin
Begin
begin
case @a<=12
select @i=1
when @a=1 then
while(i<=31)
set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=2 then
begin
If ((@year%4=0 ) and (@year%100<>0) or (@year%400=0))
if ((@Fyear%4=0 ) and (@Fyear%100<>0) or (@Fyear%400=0)
begin
while(i<=29) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
else
while(i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
when @a=3 then
while(i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=4 then
while(i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=5 then
while(i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=6 then
while(i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=7 then
while(i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
when @a=8 then
while(i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=9 then
while(i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=10 then
while(i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=11 then
while(i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=12 then
while(i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
End
Begin Tran
insert into account (period) values (@iperiod )
insert into member_account (period)values(@iperiod)
insert into account (period) values(@iperiod)
insert into bonus_account(period) values(@iperiod)
insert into dealer_account(period) values(@iperiod)
if @@rowcount=0
Begin
Rollback Tran
Return (-1)
set @errmsg='提交事务失败，回滚事务'
End
Else
Begin
Commit Tran
Return (0)
End
End
End
GO
问题点数：20、回复次数：7Top
1 楼coolingpipe（冷箫轻笛）回复于 2006-06-01 10:52:56 得分 0
case 使用有问题Top
2 楼itblog（Just for wife!）回复于 2006-06-01 10:55:01 得分 0
CREATE PROCEDURE rw_period_ini @year int,@errmsg varchar(500) output AS
Declare @iperiod int,@Fyear int, @a int,@i int
Begin
If exists(select period from period where period=@iperiod)
Set @errmsg='该期间已经存在，不能重复添加'
Else
Begin
begin
If exists(select period from period where period='0') /*期间表为空*/
Select svalue from sysconfig where @Fyear =svalue /*取系统设置表中的系统启用日期作为当前日期*/
Else
Select @Fyear=max(@iperiod) from period
end
Begin
Begin
begin
case @a<=12
select @i=1
when @a=1 then
while(i<=31)
set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=2 then
begin
If ((@year%4=0 ) and (@year%100<>0) or (@year%400=0))
if ((@Fyear%4=0 ) and (@Fyear%100<>0) or (@Fyear%400=0)
begin
while(i<=29) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
else
while(i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
when @a=3 then
while(i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=4 then
while(i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=5 then
while(i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=6 then
while(i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=7 then
while(i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
when @a=8 then
while(i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=9 then
while(i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=10 then
while(i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=11 then
while(i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=12 then
while(i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
End
Begin Tran
insert into account (period) values (@iperiod )
insert into member_account (period)values(@iperiod)
insert into account (period) values(@iperiod)
insert into bonus_account(period) values(@iperiod)
insert into dealer_account(period) values(@iperiod)
if @@rowcount=0
Begin
Rollback Tran
Return (-1)
set @errmsg='提交事务失败，回滚事务'
End
Else
Begin
Commit Tran
Return (0)
End
End
End
GO
Top
3 楼itblog（Just for wife!）回复于 2006-06-01 10:55:24 得分 0
CREATE PROCEDURE rw_period_ini @year int,@errmsg varchar(500) output AS
Declare @iperiod int,@Fyear int, @a int,@i int
Begin
If exists(select period from period where period=@iperiod)
-----------------------------------------------@iperiod初值为0,这句应该有错~
Set @errmsg='该期间已经存在，不能重复添加'
Else
Begin
begin
If exists(select period from period where period='0') /*期间表为空*/
Select svalue from sysconfig where @Fyear =svalue /*取系统设置表中的系统启用日期作为当前日期*/
-------------------------------------------svalue从哪里来的？
Else
Select @Fyear=max(@iperiod) from period
end
Begin
Begin
begin
-------------------@a没有赋值，初值为0，以下判断都没用~
case @a<=12
select @i=1
when @a=1 then
while(i<=31)-----i变量写法不对
set @i=@i+1;Top
4 楼jueranliqv（MilanNello）回复于 2006-06-01 11:01:27 得分 0
begin
select @a=0
case @a<=12
select @i=1
when @a=1 then
while(@i<=31)
set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=2 then
begin
If ((@year%4=0 ) and (@year%100<>0) or (@year%400=0))
if ((@Fyear%4=0 ) and (@Fyear%100<>0) or (@Fyear%400=0)
begin
while(@i<=29) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
else
while(@i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
when @a=3 then
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=4 then
while(@i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=5 then
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=6 then
while(@i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=7 then
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
when @a=8 then
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=9 then
while(@i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=10 then
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=11 then
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=12 then
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
End
这个改了怎么还有错误？？
Top
5 楼jueranliqv（MilanNello）回复于 2006-06-01 11:09:53 得分 0
CREATE PROCEDURE rw_period_ini @year int,@errmsg varchar(500) output AS
Declare @iperiod int,@Fyear int, @a int,@i int
Begin
If exists(select period from period where period=@iperiod)
Set @errmsg='该期间已经存在，不能重复添加'
Else
Begin
begin
If exists(select period from period where period='0') /*期间表为空*/
Select svalue from sysconfig where @Fyear =svalue /*取系统设置表中的系统启用日期作为当前日期*/
Else
Select @Fyear=max(@iperiod) from period
end
Begin
Begin
begin
select @a=0
select case @a<=12
select @i=1
when @a=1 then
while(@i<=31)
set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=2 then
begin
If ((@year%4=0 ) and (@year%100<>0) or (@year%400=0))
if ((@Fyear%4=0 ) and (@Fyear%100<>0) or (@Fyear%400=0)
begin
while(@i<=29) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
else
while(@i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
when @a=3 then
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=4 then
while(@i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=5 then
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=6 then
while(@i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=7 then
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
when @a=8 then
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=9 then
while(@i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=10 then
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=11 then
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
when @a=12 then
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
End
Begin Tran
insert into account (period) values (@iperiod )
insert into member_account (period)values(@iperiod)
insert into account (period) values(@iperiod)
insert into bonus_account(period) values(@iperiod)
insert into dealer_account(period) values(@iperiod)
if @@rowcount=0
Begin
Rollback Tran
Return (-1)
set @errmsg='提交事务失败，回滚事务'
End
Else
Begin
Commit Tran
Return (0)
End
End
End
GOTop
6 楼paoluo（一天到晚游泳的鱼）回复于 2006-06-01 11:36:36 得分 0
應該還有問題吧。Top
7 楼wangtiecheng（不知不为过，不学就是错！）回复于 2006-06-01 12:52:45 得分 0
CREATE PROCEDURE rw_period_ini @year int,@errmsg varchar(500) output AS
Declare @iperiod int,@Fyear int, @a int,@i int
Begin
If exists(select period from period where period=@iperiod)
Set @errmsg='该期间已经存在，不能重复添加'
Else
Begin
begin
If exists(select period from period where period='0') /*期间表为空*/
Select svalue from sysconfig where @Fyear =svalue /*取系统设置表中的系统启用日期作为当前日期*/
Else
Select @Fyear=max(@iperiod) from period
end
Begin
Begin
begin
select @a=1
while(@a<=12)
set @a=@i+1
select @i=1
if @a in(1,3,5,7,8,10,12)
begin
print 1
while(@i<=31) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
else
if @a in(4,6,9,11)
begin
while(@i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
else
if @a in(2)
begin
If ((@year%4=0 ) and (@year%100<>0) or (@year%400=0))
if ((@Fyear%4=0 ) and (@Fyear%100<>0) or (@Fyear%400=0))
begin
while(@i<=29)
begin
set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
end
else
while(@i<=30) set @i=@i+1;
Insert into period (period,begintime,endtime,bend) values (@iperiod,@Fyear,@year,'0')
end
end
End
Begin Tran
insert into account (period) values (@iperiod )
insert into member_account (period)values(@iperiod)
insert into account (period) values(@iperiod)
insert into bonus_account(period) values(@iperiod)
insert into dealer_account(period) values(@iperiod)
if @@rowcount=0
Begin
Rollback Tran
Return (-1)
set @errmsg='提交事务失败，回滚事务'
End
Else
Begin
Commit Tran
Return (0)
End
End
End
end
GO
Top
