Another alternative follows. Note that the error message contains details such as

the parameter values at the time of error and the name of the stored procedure

(or you could refer to a section of SP). You might also consider adding details

like the userid.

The point of this is to show that you CAN get error details out of a stored procedure,

even though the default return value is only an INT datatype. The example shows a

raiserror and a print of the error message - in some cases you may want to raise the

error within a stored procedure, while at other times you may want to handle it

outside the SP, such as the print statement that could have just as easily handed

the error message to your calling code or to a logging process.

[font="Courier New"]

IF EXISTS (SELECT * FROM dbo.sysobjects

WHERE id = OBJECT_ID(N'[dbo].[usp_errorhandling]')

AND OBJECTPROPERTY(id,N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[usp_errorhandling]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--------------------------------------------------------------------

--Purpose:Error handling example

--Changes:

--Bill Nye4/29/08Created

--------------------------------------------------------------------

CREATE PROCEDURE [dbo].[usp_errorhandling]

@Branch int ,

@Division int ,

@errormsg varchar(1000) output

AS

SET NOCOUNT ON

DECLARE @errorid int

Select 1/0-- Purposely cause error

Set @errorid=@@Error

IF @errorid <> 0

BEGIN

SELECT @errormsg=description FROM master.dbo.sysmessages

WHERE error=@errorid

SET @errormsg='Error updating when Division_ID='

+ Cast(@Division as char(1)) + ' and Branch_ID='

+ Cast(@Branch as varchar(3)) + ' in usp_errorhandling: '

+ @errormsg

RAISERROR(@errormsg,16,1)

END

GO

-------------------------------

DECLARE @rc Int, @errormsg varchar(1000)

EXEC @rc = [dbo].[usp_errorhandling] 999, 9, @errormsg OUTPUT

Print @errormsg

Print @rc

GO

DROP PROCEDURE [dbo].[usp_errorhandling]-- Cleanup[/font]