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
