quote:

---

I've had to deal with are errors like PK violations ... I would like to figure out how to trap these kinds of errors in TSQL and deal with them before my procs return control. For other ways of dealing with errors, see the master database and the SQL Server's own sp_ procs.

---

To test for primary key violations I just use syntax like

IF NOT EXISTS (SELECT 1 FROM MyTable WHERE MyField = @Value)

    BEGIN

        INSERT MyTable(My Field) VALUES (@Value)

        RETURN 1

    END

ELSE

    BEGIN

        RAISERROR('My bespoke PK Error',10,1)

        RETURN 0

END

I raise bespoke errors simply because I want to raise them with a lower severity level than would cause an stoppage.

Generally, most things can be trapped but the more error trapping is done at the SQL end the bigger the performance hit.

If there are going to be multiple apps hitting a database then I put more error trapping in the database and middle tier. If there are very few apps then I expect the app to take the burden of the error trapping.