mlcktmguy
asked on
Stored Procedure Error Trapping, Return Codes
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:
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?
Wrote my second stored procedure ever. It inserts a record into one of the database tables. It currently looks like this:
USE [JTSConversion]
GO
/****** Object: StoredProcedure [dbo].[spAddNameAddressWork] Script Date: 7/30/2015 9:55:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spAddNameAddressWork]
@ID int,
@ControlNumber int,
@SequenceNumber int,
@AddrLine1 nvarchar(30),
@AddrLine2 nvarchar(30),
@AddrLine3 nvarchar(30),
@AddrLine4 nvarchar(30),
@City nvarchar(50),
@State nvarchar(2),
@Zip nvarchar(50),
@UnMailable bit,
@Bankruptcy bit,
@AddressCheckNeeded bit,
@SelectForAct20 bit,
@MuniCode smallint
As
Begin
Set NOCOUNT on;
Insert dbo.SQNA_NameAddress_Work (ID,
ControlNumber,
SequenceNumber,
AddrLine1,
AddrLine2,
AddrLine3,
AddrLine4,
City,
State,
Zip,
UnMailable,
Bankruptcy,
AddressCheckNeeded,
SelectForAct20,
MuniCode)
Values (@ID,
@ControlNumber,
@SequenceNumber,
@AddrLine1,
@AddrLine2,
@AddrLine3,
@AddrLine4,
@City,
@State,
@Zip,
@UnMailable,
@Bankruptcy,
@AddressCheckNeeded,
@SelectForAct20,
@MuniCode)
END
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER