Cursor loop is broken

[Søren Larsen]

In a stored procedure (SP1) I am looping through a cursor with records
from Table1. Each record in the cursor is inserted into Table2.
Insert trigger on Table2 is inserting the record into Table3 (in
another DB).
In the insert trigger on Table3, a series of checks are done on the
inserted record and in case of an error, an email is sent and the
trigger returns.
This break the cursorloop in SP1 and the rest of the records in the
cursor is not treated.
How do I make sure that all records are treated?
This is the flow:
-- SP1 ---------------------------------
DECLARE csrListe CURSOR FOR SELECT felt1 FROM Table1
OPEN csrListe
-- The first record is treated here....
:
-- Treat the rest
WHILE @@FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM csrListe INTO @feltet
IF @@FETCH_STATUS = 0 BEGIN
blah-blah-blah
INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat) VALUES

(@Ordrenr, @Status, @Dato, @Result)
END
END
CLOSE csrListe
DEALLOCATE csrListe
-- Table2_ITrig ---------------------------------
INSERT INTO db2.dbo.Table3 SELECT * FROM inserted
-- Table3_ITrig ---------------------------------
SET NOCOUNT ON
DECLARE @STATUS int
DECLARE @DATOTID smalldatetime
DECLARE @RESULT int
SELECT @ORDRENR = (SELECT ORDRENR FROM INSERTED)
SELECT @STATUS = (SELECT STATUS FROM INSERTED)
SELECT @DATOTID = (SELECT DATO FROM INSERTED)
SELECT @RESULT = (SELECT RESULT FROM INSERTED)
SET XACT_ABORT ON
IF NOT @STATUS IN (1,2,3,4,5,6,9,10) BEGIN
SELECT @ERR = 'ERROR - unknown status = ' + CAST(@ORDRENR as char(4))

UPDATE Table3 SET RESULTAT=2 WHERE ORDRENUMMER=@ORDRENR
EXEC @rc = master.dbo.xp_smtp_sendmail
@FROM = [e-mail]',
@TO = [e-mail]',
@priority = N'HIGH',
@subject = N'Status error',
@message = N'Status error',
@type = N'text/plain',
@server = 'smtp.here.dk'
RETURN
END
The mail is send so it must be the final RETURN that is causing the
trouble.

[Erland Sommarskog]

Søren Larsen (sb****@surfpost.dk) writes:

In a stored procedure (SP1) I am looping through a cursor with records
from Table1. Each record in the cursor is inserted into Table2.
Insert trigger on Table2 is inserting the record into Table3 (in
another DB).
In the insert trigger on Table3, a series of checks are done on the
inserted record and in case of an error, an email is sent and the
trigger returns.
This break the cursorloop in SP1 and the rest of the records in the
cursor is not treated.
How do I make sure that all records are treated?
An error in a trigger aborts the batch. Thus, in SQL 2000, there is no
way to handle the situation in T-SQL, you would need to have a client
program that reacts on the error, and restarts the loop, but leaving
out the row that causes problems. In SQL 2005, you could use TRY-CATCH
to handle the situation.

But why are you running a loop in the first place? The normal procedure
to insert rows from one table to another is to say:

INSERT tbl2 (...)
SELECT ...
FROM tbl1

Of course, this would mean that if any of the rows are erroneous, then
all rows inserted would be rolled back by the trigger on Table3. But this
can be handled in different ways. (But exactly how, it's difficult to
say as I don't know the business requirements.)

The reason you should insert all, and not run a cursor, is that performance
for a cursor can be disastrous. If we are talking less than < 100 rows, it's
may be not that big deal. If we are talking 10000 rows, it can mean a
difference in processing time of 30 minutes instead of 30 seconds.
-- Table3_ITrig ---------------------------------
SET NOCOUNT ON
DECLARE @STATUS int
DECLARE @DATOTID smalldatetime
DECLARE @RESULT int
SELECT @ORDRENR = (SELECT ORDRENR FROM INSERTED)
SELECT @STATUS = (SELECT STATUS FROM INSERTED)
SELECT @DATOTID = (SELECT DATO FROM INSERTED)
SELECT @RESULT = (SELECT RESULT FROM INSERTED)

This trigger is poorly implemented. A trigger fires once per statement,
and must be able to handle multi-row operations.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx