Schleid Alex

Schleid Alex

  • NA
  • 361
  • 48.2k

Adding transaction to stored procedures

May 24 2021 4:22 PM
I really need your help with transaction in the following stored procedure.
  1. ALTER PROCEDURE [dbo].[UP_SAVE_EMPLOYEE_DETAILS]    
  2.                                             @StoreID int,    
  3.                                             @StoreBranchID int,    
  4.                                             @DepartmentID int,    
  5.                                             @FirstName nvarchar(30),    
  6.                                             @LastName nvarchar(20),    
  7.                                             @PhoneNumber nchar(15),    
  8.                                             @Email nvarchar(50),    
  9.                                             @Street nvarchar(50),    
  10.                                             @City nvarchar(25),    
  11.                                             @State nchar(15),    
  12.                                             @Country nvarchar(15),    
  13.                                             @IsEmployee bit,    
  14.                                             @IsCustomer bit,    
  15.                                             @IsSupplier bit,    
  16.                                             @IsReseller bit    
  17.                                             AS    
  18. SET XACT_ABORT ON    
  19.     
  20. DECLARE @PDID INT    
  21. DECLARE @ADDID INT    
  22.     
  23. --BEGIN TRANSACTION     
  24.                     EXEC @PDID = UP_INSERT_PERSONAL_DATA    
  25.                                                                 @FirstName,    
  26.                                                                 @LastName,    
  27.                                                                 @PhoneNumber,    
  28.                                                                 @Email    
  29.                                 
  30.     
  31.                     EXEC UP_INSERT_PERSONAL_DATA_RELATIONSHIP    
  32.                                                                  @PDID,    
  33.                                                                  @StoreID,    
  34.                                                                  @StoreBranchID,    
  35.                                                                  @DepartmentID,    
  36.                                                                  @IsEmployee,    
  37.                                                                  @IsCustomer,    
  38.                                                                  @IsSupplier,    
  39.                                                                  @IsReseller    
  40.                                                 
  41.                                     
  42.                     EXEC @ADDID = UP_INSERT_ADDRESS @PDID,    
  43.                                                     0,    
  44.                                                     Street,    
  45.                                                     @City,    
  46.                                                     @State,    
  47.                                                     @Country,    
  48.                                                     @PhoneNumber,    
  49.                                                     '000-000-0000'    
  50.     
  51.                     IF (@PDID > 1 AND @ADDID > 1)    
  52.                         RETURN 1    
  53.                     ELSE     
  54.                         RETURN 0    
  55. --COMMIT TRANSACTION  
when I try the transaction I have the following error.
 
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
 
Thank you!

Answers (3)