How to stop execution of a stored procedure and cancel transaction
Try and execute the SQL below on SQL Server 2005 (use a test database as 2 stored procedures are created).
When "SecurityCheck" raises the error I need "SomeAction" to ROLLBACK TRANSACTION and stop executing. "SomeAction" should never reach the PRINT statement.
Thanks,
Jimmy
CREATE PROCEDURE [dbo].[SecurityCheck]
AS
BEGIN
RAISERROR('HEY! You are not allowed to print anything!', 16, 1)
END
GO
CREATE PROCEDURE [dbo].[SomeAction]
AS
BEGIN
BEGIN TRANSACTION
EXEC SecurityCheck
PRINT 'This should never be printed'
COMMIT TRANSACTION
END
GO
EXEC [dbo].[SomeAction]
When "SecurityCheck" raises the error I need "SomeAction" to ROLLBACK TRANSACTION and stop executing. "SomeAction" should never reach the PRINT statement.
Thanks,
Jimmy
CREATE PROCEDURE [dbo].[SecurityCheck]
AS
BEGIN
RAISERROR('HEY! You are not allowed to print anything!', 16, 1)
END
GO
CREATE PROCEDURE [dbo].[SomeAction]
AS
BEGIN
BEGIN TRANSACTION
EXEC SecurityCheck
PRINT 'This should never be printed'
COMMIT TRANSACTION
END
GO
EXEC [dbo].[SomeAction]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
AS
BEGIN
RAISERROR('HEY! You are not allowed to print anything!', 16, 1)
END
GO
CREATE PROCEDURE [dbo].[SomeAction]
AS
BEGIN
BEGIN TRANSACTION
EXEC SecurityCheck
if (@@ERROR > 0) AND (@@ROWCOUNT <> 0)
COMMIT TRANSACTION
return
HANDLE_ERROR:
ROLLBACK TRANSACTION
END
GO
Hope this helps
Aash.