I am very new to SQL Server.
Wrote my second stored procedure ever. It inserts a record into one of the database tables. It currently looks like this:
/****** Object: StoredProcedure [dbo].[spAddNameAddressWork] Script Date: 7/30/2015 9:55:41 PM ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[spAddNameAddressWork]
Set NOCOUNT on;
Insert dbo.SQNA_NameAddress_Work (ID,
It works great, except when it doesn't
. When all is well, it adds a record. However, in my testing I ran into a situation where the sp encountered an error because I had attempted to insert a record with a duplicate key. The result, application crash with no recovery.
I have read about Begin/End and Try/Catch but don't know exactly how to implement them in this sp. Ideally the sp would return to the application program either the ID of the record that was successfully inserted or a code and possibly error message indicating an error occurred.
Depending on the error the application could show the user what issue was encountered and proceed. In my VBA apps I have a list error return codes in my error handler. Based on the code returned from a executed command, the app can react accordingly. Is there a list of possible error return codes in SQL. Or at least of the common one's like 'Attempt to insert a duplicate record'?
How could I revise my sp for better error handling?