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?
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tapan PattanaikSenior EngineerCommented:
Hi, mlcktmguy

Please add the duplicate validation to the stored procedure.

Example for validating duplicate ControlNumber        

If your control number already exists ,then it  will not allow you to insert the same number to the database, and return out put message 'ControlNumber already exists'


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,
       @Result  int out,
	@ResultText  varchar(100) out
As

Begin

Set NOCOUNT on;

IF Not EXISTS (select ControlNumber from dbo.SQNA_NameAddress_Work where ControlNumber= @ControlNumber and @ID!=@ID)
BEGIN
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)

	set @Result=1;
       set @ResultText='New Record created successfully';
   END

ELSE
           BEGIN
		set @Result=0;
		set @ResultText='ControlNumber already exists';
	END	

END

Open in new window

0
Scott PletcherSenior DBACommented:
2627 is the normal error number for a violation of a unique constraint.  Here's a general overview of how to use Try ... Catch.  Some will whine about returning data using the return code; if you prefer, set up an OUTPUT parameter that returns the value instead.

[quote]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

Set NOCOUNT on;
Set ANSI_PADDING on;
Set ANSI_WARNINGS on;
Set ARITHABORT on;
Set CONCAT_NULL_YIELDS_NULL on;
Set NUMERIC_ROUNDABORT off;

Begin Try
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)
Return SCOPE_IDENTITY() --positive number = successful
--Return @ID --whichever you want to return
End Try
Begin Catch
Declare @error_number int
SELECT @error_number = ERROR_NUMBER()
Return -1*@error_number --negative number = error
End Catch
GO[/quote]

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Thanks you, both solutions work.  The second used Try/Catch, which I was hoping to learn more about.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.