Link to home
Start Free TrialLog in
Avatar of mlcktmguy
mlcktmguyFlag for United States of America

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:

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

Open in new window


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
Avatar of Tapan Pattanaik
Tapan Pattanaik
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mlcktmguy

ASKER

Thanks you, both solutions work.  The second used Try/Catch, which I was hoping to learn more about.