declare cursors - 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

SQL Server 2000 Forums

SQL Server Development (2000)

declare cursors

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2001-09-12 : 22:33:03

i have a small problem may not be to u
i have a stored procedure which has a parameter @act
based on the value of @act my cursor declare will change
if i will write a block it will like this

declare @act varchar(8)
begin
set @act='01924762'
if (@act is not null)
begin
select a.Unit_Id,acctno from subscriber a ,charges b where a.Chg_Id=b.Chg_Id and status='C' and
a.acctno=@act union select unit_id,acctno from unit_events where Bill_Flg='N' and acctno=@act
order by acctno,a.unit_id
end
else
begin
select a.Unit_Id,acctno from subscriber a ,charges b where a.Chg_Id=b.Chg_Id and status='C'
union select unit_id,acctno from unit_events where Bill_Flg='N'
order by acctno,a.unit_id
end
end

if i declare a cursor

if (@act is null)
begin
declare Acct_Unit cursor for
select a.Unit_Id,acctno from subscriber a ,charges b where a.Chg_Id=b.Chg_Id and status='C' union select unit_id,acctno from unit_events where Bill_Flg='N' order by acctno,a.unit_id
end

else

if (@act is not null)
begin
declare Acct_Unit cursor for
select a.Unit_Id,acctno from subscriber a ,charges b where a.Chg_Id=b.Chg_Id and status='C' and a.acctno=@act union select unit_id,acctno from unit_events where Bill_Flg='N' and acctno=@act order by acctno,a.unit_id
end

but the problem is when i run the stored procedure it gives a error
cursor already open or
cursor with this name already exist

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources