Errors in SQL Server. How prevent them?

Im designing a dynamic website with Dreamweaver MX, ASP VBScript language, SQL 2000 Server and PWS as a server. The site is not available in Internet yet.

For the moment I have created all the main functions of the website. Almost all the actions that the site leads to term it does it with stored procedures (sp) on SQL Server. Those SP works well from the web application, and all the actions (to insert data, to modify data, to check data, etc.) works perfect. But I have been advised that I learn to capture the possible errors that SQL Server can return to user, and to avoid that the site fell. Someone could explain me what kind of errors can return me? And how can I solve it?

I can not understand why I need to know how to capture those errors because, if were managing to happen, the browser show us a message of error and the user try again the operation, as I have seen at many sites. After that, I should quarrel to discover why these errors happen, and then I try to solve it, or take the aid of an expert administrator. Or, can I really to prevent these potential errors and to minimize their effects?

In the other hand, I cant understand why my stored procedures are able to return errors or to bring problems to my web application, now it works well.

Thank you.

Here is an example of one of my stored procedures:

CREATE PROCEDURE check_mail_password_inscription
@.E-mail varchar(50), @.Password char(10),
@.Offer_num int, @.Date datetime, @.Motives_interest varchar(250)
As
Declare @.Buyer_num int

If Exists(select E-mail from Buyers where E-mail = @.E-mail
and Password = @.Password)
begin

select @.Buyer_num = Buyer_num
from Buyers
where E-mail = @.E-mail
and Password = @.Password

select @.Buyer_num AS Buyer_num, 1 AS Value
INSERT INTO Inscriptions_in_offers (Offer_num, Buyer_num, Date, Motives_interest)
VALUES (@.Offer_num, @.Buyer_num, @.Date, @.Motives_interest)
end
else
begin
select 0 AS Value
endAny data update can cause a deadlock and there is very little you can do to prevent it - you can minimise it but not prevent. Systems that are not designed with this in mind usually have a lot of problems when they get a few concurrent users.

Your system should always take account of the possibility of any operation failing.

In your SP what happens if the insert fails? to you still want to return a resultset?
And why return a single row resultset rather than output parameters which are much faster.
You are also returning a different shape resultset for the two paths which is a bit odd.

I would guess that you also need to read up on transaction control.|||What you recommend me to minimize the errors in my stored procedures? Now they are totally helpless. I need to use BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION, @.@.ERROR variable, RAISERROR, RETURN statement to force an unconditional exit or sp_addmessage stored procedure? Or all together where this is possible?

If my sp fails and the action cannot be done, I need communicate it to my web application so that the user tries again the operation.

I only know this tools in general, never I used to. Would you mind give me a complete example with my stored procedure please?

CREATE PROCEDURE check_mail_password_inscription
@.E-mail varchar(50), @.Password char(10),
@.Offer_num int, @.Date datetime, @.Motives_interest varchar(250)
As
Declare @.Buyer_num int

If Exists(select E-mail from Buyers where E-mail = @.E-mail
and Password = @.Password)
begin

select @.Buyer_num = Buyer_num
from Buyers
where E-mail = @.E-mail
and Password = @.Password

select @.Buyer_num AS Buyer_num, 1 AS Value
INSERT INTO Inscriptions_in_offers (Offer_num, Buyer_num, Date, Motives_interest)
VALUES (@.Offer_num, @.Buyer_num, @.Date, @.Motives_interest)
end
else
begin
select 0 AS Value
end

I have a manual of SQL Server that says that exist two types of transactions: explicit and implicit. Explicit transaction means that begin with the BEGIN TRANSACTION statement and end with the COMMIT TRANSACTION statement. Implicit transaction means that, more or less, if you don t use the BEGIN TRANSACTION and COMMIT TRANSACTION statements, nor do you turn on implicit transactions, SQL Server autocommits the transaction. With this, I understand that is not necessary to write the statements begin tr and commit tr in my sp. It is like that?


Thank you,
Cesar