Error Checking with Emails

[sdempsey]

I have a table with stores the unique identifier for each customer that is to recieve an email. I then have a stored proc which sends out the emails. However there was an error is the SP and too many emails were sent out. I am abit unsure how to error check the stored procedure.


WHILE (@@FETCH_STATUS=0)
BEGIN
set @rec = 'email address'


if( @P_size >0 and @A_register_size>0 and @C_register_size>0)
BEGIN

send email
END

Else If ( @P_size =0 and @A_register_size=0 and @C_register_size=0)
BEGIN
send email

END



FETCH NEXT (the variables)
END

However when one of the data types which was meant to be int was varchar. The loop was never exited and this caused many emails to be sent.

I have now fixed this however I am worried this may happen again and was wondering the best way to catch errors and what you should do with them i.e. to prevent the SP from still looping.

Any help or pointers in the right direction. I know that you can use @@Error but I am not really sure what to do with it.

Thanks in advance.

 

[nomadicalloy]

Please look at this article :

http://www.sqlteam.com/item.asp?ItemID=2463

SQL Help Blog

http://www.xpherion.com/blog.asp

 

[sdempsey]

I have looked through the articles and still cant seem to do what I want:-

Open cursor
fetch the variables into it (the error is with one of the variables)

ADDDED
if (@@Error<>0
BEGIN
send me an email and do nothing else
END

ELSE
do everything below


WHILE (@@FETCH_STATUS=0)
BEGIN
set @rec = 'email address'


if( @P_size >0 and @A_register_size>0 and @C_register_size>0)
BEGIN

send email
END

Else If ( @P_size =0 and @A_register_size=0 and @C_register_size=0)
BEGIN
send email

END



FETCH NEXT (the variables)
END

I can see there is an error in query analyser however I dont recieve my email. Can the cursor not tell there is an error in a group of rows and only in a single row.

Thanks in advance