SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 cursor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jogin malathi
Posting Yak Master

India
117 Posts

Posted - 04/12/2007 :  01:01:28  Show Profile  Reply with Quote
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

Sweden
30277 Posts

Posted - 04/12/2007 :  01:05:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Maybe, if you posted the actual code, we get a chance to decide and investigate your problem.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

jogin malathi
Posting Yak Master

India
117 Posts

Posted - 04/12/2007 :  01:51:42  Show Profile  Reply with Quote
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

Edited by - jogin malathi on 04/12/2007 01:54:07
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000