Use of Begin Commit Transaction ROLLBACK in a Stored procedure SQL Server
You can see how we can create a stored Procedure and pass the parameters dyanmically. How we can roll back if transaction failed due to any reason.
Create PROCEDURE [dbo].[Save_PurchaseOrder]
(
@UserId int,
@Vendorid int,
@CounterPerson varchar(50),
@ShippedTo varchar(20),
@ShippingCost varchar(20),
@CustomerName varchar(50),
@Location varchar(50),
@OrderStatus char(1),
@ChangeStatusById int=0,
@ChangeStatusByName varchar(50)='',
@MAXID nvarchar(200) output,
@OtherVendor nvarchar(50),
@OtherTechnician nvarchar(50)
)
AS
BEGIN
BEGIN TRANSACTION
INSERT INTO [tblPurchaseOrder]
([UserId],[Vendorid],[PartsStatus]
,[ShippedTo],[ShippingCost],[CustomerName],[Location]
,[OrderStatus],[OrderDate],[ChangeStatusById],[ChangeStatusByName],OtherVendor,OtherTechnician)
VALUES
(@UserId ,@PartsStatus
,@ShippedTo,@ShippingCost,@CustomerName,@Location
,@OrderStatus,GETDATE(),@ChangeStatusById,@ChangeStatusByName,@OtherVendor,@OtherTechnician)
SELECT @MAXID=SCOPE_IDENTITY()
IF @@ERROR<>0
BEGIN
ROLLBACK
SET @MAXID=0
RETURN
END
COMMIT TRANSACTION
END
Related Alrticles