Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jogin malathi
Posting Yak Master
117 Posts |
Posted - 2007-04-12 : 01:01:28
|
I hav a cursorwhich i want to execute in procedure more than onceFor that i am repeating the cursorBy which no of lines in procedure is increasedIs their any way to call that cursor without repeating the cursor codeMalathi Rao |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-12 : 01:05:59
|
Maybe, if you posted the actual code, we get a chance to decide and investigate your problem.Peter LarssonHelsingborg, Sweden |
|
|
jogin malathi
Posting Yak Master
117 Posts |
Posted - 2007-04-12 : 01:51:42
|
else if (@operator = 'U')begin declare @Leavecode Varchar(7) select @Leavecode=LeaveCode from l_LeaveType where LeaveTypeId=@Id if (@Leavecode = @Code) begin begin transaction LeaveType update l_LeaveType set LeaveCode=@Code, Description=@Description, NoOfDays=@NoOfDays, Status=@Status where LeaveTypeId=@Id declare @ltid int declare @eno int declare @stat int set @stat=@status declare dc1 cursor for select EmployeeId ,LeaveTypeId from l_LeaveBalance where LeaveTypeId=@Id begin open dc1 fetch dc1 into @eno,@ltid while @@fetch_status=0 begin PRINT @eno print @ltid declare @no1 decimal DECLARE @sat int select @no1=NoOfLeaves from l_LeaveBalance where EmployeeId=@eno and LeaveTypeId=@ltid print @no1 if not exists(select * from l_leavebalance where EmployeeId=@eno and LeaveTypeId=@ltid and Status = @stat) begin if @stat = 0 begin print @stat update l_EmployeeBalance set LeaveBalance=LeaveBalance- @no1 where EmployeeId=@eno end else begin print @stat update l_EmployeeBalance set LeaveBalance=LeaveBalance+@no1 where EmployeeId=@eno end end fetch dc1 into @eno,@ltid end close dc1 deallocate dc1 end update l_LeaveBalance set status=@stat where LeaveTypeId=@ltid --Erro Handling IF @@ERROR <> 0 BEGIN --print'Returns 0 to the calling program to indicate failure.' ROLLBACK TRAN LeaveType SET @res = 0 END ELSE BEGIN --print'updated' COMMIT TRAN LeaveType SET @res = 1 END ENDElseif not exists(select * from l_LeaveType where LeaveCode=@Code)begin begin transaction LeaveType update l_LeaveType set LeaveCode=@Code, Description=@Description, NoOfDays=@NoOfDays, Status=@Status where LeaveTypeId=@Id declare @ltid1 int declare @eno1 int declare @stat1 int set @stat1=@status declare dc2 cursor for select EmployeeId ,LeaveTypeId from l_LeaveBalance where LeaveTypeId=@Id begin open dc2 fetch dc2 into @eno1,@ltid1 while @@fetch_status=0 begin PRINT @eno1 print @ltid1 declare @no2 decimal DECLARE @sat1 int select @no2=NoOfLeaves from l_LeaveBalance where EmployeeId=@eno1 and LeaveTypeId=@ltid1 print @no2 if not exists(select * from l_leavebalance where EmployeeId=@eno1 and LeaveTypeId=@ltid1 and Status = @stat1) begin if @stat1 = 0 begin print @stat1 update l_EmployeeBalance set LeaveBalance=LeaveBalance- @no2 where EmployeeId=@eno1 end else begin print @stat update l_EmployeeBalance set LeaveBalance=LeaveBalance+@no2 where EmployeeId=@eno1 end end fetch dc2 into @eno1,@ltid1 end close dc2 deallocate dc2 end update l_LeaveBalance set status=@stat where LeaveTypeId=@ltid1 --Error handling IF @@ERROR <> 0 BEGIN --print'Returns 0 to the calling program to indicate failure.' ROLLBACK TRAN LeaveType SET @res = 0 END ELSE BEGIN --print'Returns 1 to the calling program to indicate success.' --PRINT'ROW UPDATED' COMMIT TRAN LeaveType SET @res = 1 END END ELSE BEGIN --print'Return 2 to the calling program to indicate record already exists. ' set @res = 2 END ENDquote: Originally posted by Peso Maybe, if you posted the actual code, we get a chance to decide and investigate your problem.Peter LarssonHelsingborg, Sweden
Malathi Rao |
|
|
|
|
|
|
|