October 5, 2015 at 5:44 pm
In t-sql 2012, I have the following sql that I would like the following to occur:
1. commit or rollback a transaction based upon the results of a calculation listed below,
2. I would like to have a message appear if the commit was successful or the rollback needed to occur. I basically want a way
to be able to tell from messages if a rollback occurred or a commit happened.
DECLARE @TransactionName varchar(20) = 'Transaction1';
@STARTLOCKERCNT INT = 0, @LOCKDIFCNT INT = 0, @ENDLOCKERCNT INT = 0
DECLARE @lockmap TABLE (lockID int NOT NULL PRIMARY KEY,
schoolID int NOT NULL,
UNIQUE(schoolID,lockID)
)
BEGIN TRAN @TransactionName
SELECT @STARTLOCKERCNT = COUNT(*)
from test.dbo.Locker where schoolID = @SchoolID
SELECT @LOCKDIFCNT = COUNT(DISTINCT G.lockerNumber)
FROM dbo.in1 G
JOIN test.dbo.School s on s.type = G.schoolNumber
and s.type = @SchoolNumber
JOIN test.dbo.Locker l on l.schoolID = s.schoolID
and G.lockerNumber not in
(select number from test.dbo.Locker where schoolID = @SchoolID)
INSERT test.dbo.Lock(schoolID,serialNumber)
OUTPUT inserted.lockID, inserted.schoolID
INTO @lockmap (lockID, schoolID)
select distinct l.schoolid,G.lockSerialNumber
FROM dbo.GIMST0125CampusLockerPopulation G
JOIN test.dbo.School s on s.type = G.schoolNumber
and s.type = '999'
JOIN test.dbo.Locker l on l.schoolID = s.schoolID
and l.schoolID = 'III'
and G.schoolNumber = s.type
and G.lockerNumber not in
(select number from test.dbo.Locker where schoolID = @SchoolID)
order by l.schoolid,G.lockSerialNumber
--INSERT test.dbo.Locker(schoolID, number, lockID)
SELECT DISTINCT s.schoolID, G.lockerNumber, lm.lockID
FROM dbo.GIMST0125CampusLockerPopulation G
JOIN test.dbo.School s ON G.schoolnumber = s.type
JOIN @lockmap lm ON s.schoolID = lm.schoolID
WHERE G.lockerNumber IS NOT NULL
order by s.schoolID, G.lockerNumber, lm.lockID
SELECT @ENDLOCKERCNT = COUNT(*)
from test.dbo.Locker where schoolID = @SchoolID
SELECT @STARTLOCKERCNT, @LOCKDIFCNT, @ENDLOCKERCNT
IF @STARTLOCKERCNT + @LOCKDIFCNT = @ENDLOCKERCNT
COMMIT TRAN @TransactionName;
ELSE
select @SchoolID
ROLLBACK TRAN @TransactionName;
Thus can you modify the sql I just listed above so that I meet the goals that I just listed above?
October 5, 2015 at 6:15 pm
Use a TRY/CATCH block and return the message in the CATCH block?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply