I have written the following Stored Procedure and need input as to what could be wrong with my coding or is there a possibility that not getting the correct results is not in the procedure at all? -
ALTER PROCEDURE spIssueScannedTickets
-
@iEventID int,
-
@MemberNum nvarchar(12),
-
@BatchSize int,
-
@FirstNumber nvarchar(12),
-
@LastNumber nvarchar(12),
-
@SlotsBalance int,
-
@TableBalance int,
-
@BonusBalance int,
-
@UserID int
-
AS
-
DECLARE @Result int
-
SET @Result = 0
-
BEGIN TRANSACTION
-
if (@SlotsBalance>0)
-
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
-
VALUES (GETDATE(),@iEventID,@MemberNum, 'Slots', @SlotsBalance, 0, @UserID)
-
SELECT @Result = @@error IF @Result <> 0 BEGIN ROLLBACK TRANSACTION RETURN @Result END
-
-
if (@TableBalance>0)
-
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
-
VALUES (GETDATE(),@iEventID,@MemberNum, 'Tables', @TableBalance, 0, @UserID)
-
SELECT @Result = @@error IF @Result <> 0 BEGIN ROLLBACK TRANSACTION RETURN @Result END
-
-
if (@BonusBalance>0)
-
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
-
VALUES (GETDATE(),@iEventID,@MemberNum, 'Bonus', @BonusBalance, 0, @UserID)
-
SELECT @Result = @@error IF @Result <> 0 BEGIN ROLLBACK TRANSACTION RETURN @Result END
-
-
INSERT INTO BundlesIssued (MemberNumber, EventID, BundleSize, FirstNumber, LastNumber, DateIssued, UserID, Invalid)
-
VALUES (@MemberNum, @iEventID, @BatchSize, @FirstNumber, @LastNumber, GETDATE(), @UserID , 0x00)
-
SELECT @Result = @@error IF @Result <> 0 BEGIN ROLLBACK TRANSACTION RETURN @Result END
-
-
COMMIT TRANSACTION
-
SELECT @Result = @@error IF @Result <> 0 RETURN @Result
-
SET @Result = @BatchSize RETURN @Result
-
GO
-
As far as I am aware what I have written should give an "all or nothing situation". In other words either all tables are updated or none are.
Comments please.
|
