Cursor issue - 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 2005 Forums

Transact-SQL (2005)

Cursor issue

Author

Topic

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-05-15 : 21:47:29

Can someone tell me what's wrong with this cursor?

if @senddisputed=1
declare x cursor for select email from dbo.Users where UserType='A' and email is not null
open x
fetch next from x into @email
while @@fetch_status = 0
BEGIN
insert into emails (transno,reqid,efrom,eto,subject,body,notified,emailtype)
(select 0,0,@fromaddress,@email,'Disputed Transaction Notification',
'This Email is to alert you that you currently have '+ ltrim(str(count(distinct th.transno)))+ ' transactions in
resolve that are under the status of declined. Please log into the system and review these records.' + char(13),
null,8
from
transhdr th join cards c on c.cardid = th.cardid join users u on c.mgrid = u.userkey
join transdtl td on th.transno = td.transno
join recon r on th.transno = r.transno
where
u.email is not null
and th.reqid=1
group by
u.email)
fetch next from x into @email
END
close x
deallocate x

Results:
Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 212
A cursor with the name 'x' does not exist.
Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 215
A cursor with the name 'x' does not exist.
Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 234
A cursor with the name 'x' does not exist.
Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 235
A cursor with the name 'x' does not exist.

---
http://www.ssisdude.blogspot.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-15 : 23:01:04

Since your IF statement "if @senddisputed=1" isn't followed by a BEGIN/END block only the next line - the cursor declaration is conditionally executed. So if @senddisputed != 1 all your cursor code is executing except the DECLARE.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-15 : 23:10:55

I don't see why you need a cursor for this anyway. Can't you join dbo.Users to your query ON UserType='A' and email is not null?
Then use dbo.Users.email instead of @email?

Be One with the Optimizer
TG

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-16 : 03:14:49

Can't see the need for a cursor here:

insert into emails (transno,reqid,efrom,eto,subject,body,notified,emailtype)
select 
  0,
  0,
  @fromaddress,
  email,
  'Disputed Transaction Notification',
  'This Email is to alert you that you currently have '+ ltrim(str(count(distinct th.transno)))+ ' transactions in 
    resolve that are under the status of declined. Please log into the system and review these records.' + char(13),
  null,
  8
from transhdr th 
  join cards c 
    on c.cardid = th.cardid 
  join users u 
    on c.mgrid = u.userkey
  join transdtl td 
    on th.transno = td.transno
  join recon r 
    on th.transno = r.transno
where
  u.email is not null
  and u.UserType='A'
  and th.reqid = 1
group by
  u.email

--
Lumbago

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-05-16 : 11:19:16

I'll suggest that to owner of the proc and I see what you guys are saying. That's a SP that was give to me to fix the cursor issue.
All that I know is that they need all the records from the Users table where UserType='A' inserted into the emails table and someone changed the name of the cursor and broke the SP.

---
http://www.ssisdude.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources