USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_test]
AS
BEGIN
SET NOCOUNT ON;
--////////////////////////////////////////////
Declare @ErrorCode int
Select @ErrorCode = @@Error
Declare @TransactionCountOnEntry int
If @ErrorCode = 0
Begin
Select @TransactionCountOnEntry = @@TranCount
BEGIN TRANSACTION
End
Declare @Temp nchar(10)
SELECT @Temp = 'Insertion'
INSERT INTO dbo.Test(logo) VALUES(@Temp)
--////////////////////////////////////////////////
IF @@TRANCOUNT > @TransactionCountOnEntry
BEGIN
IF @ErrorCode = 0
COMMIT TRANSACTION
EXEC xp_sendmail @recipients=N'someone@somewhere.com', @Subject='JOb has run successfully'
ELSE
ROLLBACK TRANSACTION
EXEC xp_sendmail @recipients=N'someone@somewhere.com', @Subject='JOb has failed'
END
--/////////////////////////////////////////
END
GO
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.