Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

RAISERROR

RAISERROR:

Used to return error message back to application,system warning or error message generated by Sql server database engine.

Syntax:

RAISERROR ( { msg_id | msg_str | @local_variable }

    { ,severity ,state }

    [ ,argument [ ,...n ] ] )

    [ WITH option [ ,...n ] ]

 

msg_id:

user defined error number stored in sys.messages catalog view using sp_addmessage.  user defined number should be greater than 5000. When msg_id not specified RAISERROR raises an error message with default msg_id 50000.

msg_str:

user defined error message,The error message can have a maximum of 2,047 characters.

When msg_str is specified, RAISERROR raises an error message with an error number of 50000.

Other argiuments are severity,state ,argument,option.

 

SQL Server supports the following functions, to describe error or exception details in catch block:

ERROR_NUMBER(): The number assigned to the error.

ERROR_LINE(): The line number inside the routine that caused the error.

ERROR_MESSAGE(): The error message text, which includes the values supplied for any substitutable parameters, such as times or object names.

ERROR_SEVERITY(): The error’s severity.

ERROR_STATE(): The error’s state number.

ERROR_PROCEDURE(): The name of the stored procedure or trigger that generated the error.

Example:

-- =============================================

-- Author:        Nagnath Kendre

-- Create date: <Create Date,,>

-- Description:   Insert's Emplyee Detail, exception handlled with try catch block and RAISERROR

-- =============================================

CREATE PROCEDURE InsertEmplyeeDetails_SP

      -- Add the parameters for the stored procedure here

      @EmployeeName Nvarchar(250),

      @Country Nvarchar(50),

      @PrimaryLanguage Nvarchar(150),

      @CompanyPlant Nvarchar(150),

      @ManagerId int

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

 SET NOCOUNT ON;

 BEGIN TRY

 BEGIN TRANSACTION

  INSERT INTO Employee

([EmployeeName],[Country],[PrimaryLanguage],[CompanyPlant],[ManagerId],[CreatedDate])

 VALUES (@EmployeeName,@Country,@PrimaryLanguage,@CompanyPlant,@ManagerId,GETDATE())

 

 COMMIT TRANSACTION;

 END TRY

 

BEGIN CATCH

IF @@TRANCOUNT > 0

    ROLLBACK TRANSACTION;

 

    DECLARE @ErrorNumber INT = ERROR_NUMBER();

    DECLARE @ErrorLine INT = ERROR_LINE();

    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();

    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();

    DECLARE @ErrorState INT = ERROR_STATE();

 

    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));

    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));

 

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)

 

END CATCH

 

END

GO

 

Result:

EXEC  [dbo].[InsertEmplyeeDetails_SP]

            @EmployeeName = N'Sanskruti',

            @Country = N'INDIA',

            @PrimaryLanguage = N'Marathi',

            @CompanyPlant = N'Pune',

            @ManagerId = abd 


Invalid entry,please enter valid data.

Loading