Is it possible to raise an error in a stored procedure manually to stop execution and jump to BEGIN CATCH block? Some analog of throw new Exception() in C#.
Here is my stored procedure's body:
BEGIN TRY
BEGIN TRAN
-- do something
IF @foobar IS NULL
-- here i want to raise an error to rollback transaction
-- do something next
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@trancount > 0
ROLLBACK TRAN
END CATCH
I know one way: SELECT 1/0 But it's awful!!
you can use raiserror. Read more details here
--from MSDN
BEGIN TRY
-- RAISERROR with severity 11-19 will cause execution to
-- jump to the CATCH block.
RAISERROR ('Error raised in TRY block.', -- Message text.
16, -- Severity.
1 -- State.
);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
EDIT
If you are using SQL Server 2012+ you can use throw clause. Here are the details.
You could use THROW (available in SQL Server 2012+):
THROW 50000, 'Your custom error message', 1
THROW <error_number>, <message>, <state>
You're looking for RAISERROR.
From MSDN:
Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.
CodeProject has a good article that also describes in-depth the details of how it works and how to use it.
SQL has an error raising mechanism
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
Just look up Raiserror in the Books Online. But.. you have to generate an error of the appropriate severity, an error at severity 0 thru 10 do not cause you to jump to the catch block.
Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server 2017.
Please refer the below link
