My requirement is to setup a standard practice for our team in handling DB erros and also user defined messages. We are handling exceptions using Raiseerror method with in Try and Catch blocks.
For user defined message, we are using Sp_addmessage to add our application specific error/warning messages with error codess>80000. Ex: 80001 | Company name already exists. please check.
Here is the sample code
*********************************************************************
CREATE PROCEDURE [dbo].[usp_CompanyProfile_insert]
-- Add the parameters for the stored procedure here
(@company_name VARCHAR(50),
@company_code VARCHAR(3),
@user_id INT
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY -- Begin Try Block
-- Validate company name
IF EXISTS (SELECT company_id FROM companyprofile WHERE company_name = @company_name)
RAISERROR(90021, 16, 1)
INSERT INTO companyprofile (company_name,
company_code,
createdby)
VALUES(@company_name,
@company_code,
@user_id)
END TRY -- End Try Block
&
View Complete Post