cursor - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

cursor

Author

Topic

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-04-12 : 01:01:28

I hav a cursor
which i want to execute in procedure more than once
For that i am repeating the cursor
By which no of lines in procedure is increased
Is their any way to call that cursor without repeating the cursor code

Malathi 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 Larsson
Helsingborg, 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
END
Else
if 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
END

quote:
Originally posted by Peso

Maybe, if you posted the actual code, we get a chance to decide and investigate your problem.

Peter Larsson
Helsingborg, Sweden

Malathi Rao

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources