Re: Help Troubleshooting this T-SQL

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

John Wright (JohnWright@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
OPEN PersonCursor
FETCH NEXT FROM PersonCursor
INTO @Name, @Email,@txtMail

While @@Fetch_Status = 0
BEGIN
EXEC msdb.dbo.sp_Send_dbmail
@profile_name='WZSQLAdmin',
@Recipients = @email,
@Subject = @mySubject,
@body = @Msg,
@body_format='HTML'
--Check to see if there is a text email
IF @txtMail is not null
BEGIN
EXEC msdb.dbo.sp_Send_dbmail
@profile_name='WZSQLAdmin',
@Recipients = @txtMail,
@Subject = @mySubject,
@body = @Msg,
@body_format='HTML'
END
FETCH NEXT FROM PersonCursor
INTO @Name, @Email
END

This procedure will only send to the first person in the list (in the
PresonCursor). It will not fetch the next record. Can anyone see why?

Yes. This is how you should code your cursor loops:

DECLARE cur CURSOR STATIC LOCAL FOR
SELECT cur

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO ...
IF @@fetch_status <> 0
BREAK

-- Do your stuff
END

DEALLLOCATE cur

If you code your cursors this way, you will never commit the error you
have done twice in the SendEPNNotification procedure.

Of course, some TRY-CATCH to capture and log errors can help trouble-
shooting as well.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: Cross Tab In SQL 2K ??
    ... DECLARE @FolderID VARCHAR ... DECLARE cur CURSOR FORWARD_ONLY FOR ... FETCH NEXT FROM cur INTO @name ... EXEC sp_executesql @sql, N'@Value VARCHAR ...
    (microsoft.public.sqlserver.programming)
  • Re: Cross Tab In SQL 2K ??
    ... DECLARE @sql NVARCHAR ... DECLARE @FolderID VARCHAR ... DECLARE cur CURSOR FORWARD_ONLY FOR ... FETCH NEXT FROM cur INTO @name ...
    (microsoft.public.sqlserver.programming)
  • Re: Disable Replication, remove rowguide-column?
    ... exec sp_configure N'allow updates', 1 ... DECLARE @username varchar ... FETCH NEXT FROM list_triggers INTO @name, ... create table syssubscriptions (artid int, srvid smallint, dest_db sysname, ...
    (microsoft.public.sqlserver.replication)
  • RE: SQL 2000 instance showing up as version6.5 after running fixre
    ... exec sp_configure N'allow updates', 1 ... DECLARE @username varchar ... FETCH NEXT FROM list_triggers INTO @name, ... create table syssubscriptions (artid int, srvid smallint, dest_db sysname, ...
    (microsoft.public.sqlserver.clustering)
  • Re: MySQL VARCHAR missing value content
    ... All of the records that is supposed to FETCH is intact though... ... If the SQL is wrong, ... Procedure Division using lnk-DBI ... At the end of the game,, I do NOT need to return the info in lnk-DBI back to the DBI class - granted, using the Animator to take a look I will see different Hex value references to the Collection in the DBI Class and in this method; but they are both 'pointers' to the same object reference. ...
    (comp.lang.cobol)