Syntax error in transaction using labels
I have a stored proc with a structure similar to the following
BEGIN TRY
BEGIN TRANSACTION
IF EXISTS (SELECT Something FROM Somewhere WHERE Something = '123abc')
GOTO FINISHED
IF NOT EXISTS (SELECT Something FROM Somewhere WHERE Something = 'abc123')
SET @ReturnCode = 2
FINISHED:
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
Assume the GOTO isn't being used. I'm getting the following two errors
Line 183
Incorrect syntax near 'F'.
Line 183
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
If I wrap the last IF
with a BEGIN
and END
the stored proc开发者_Python百科 works as normal. This is fine and takes care of my syntax issues.
The problem is I have this exact stored proc on two different servers. One will always get the errors and one never gets the errors with the code as it is above.
The only thing I can think of is that there may be some setting in SQL that is turned on on one server but not the other. Has anyone ever had something like this happen before?
More details: The problematic server is out of date with no service packs installed while the second server has SP2 installed. Currently in the process of upgrading. Will update later with results.
So, as per my last edit, I installed service pack 3. This resolved the issue I was experiencing.
Apparently this was resolved in SP2. See the following links
Link to KB article for SP2 bug fixes
Link to specific bug
Why not reverse the logic on the first IF and eliminate the GOTO nonsense completely?
BEGIN TRY
BEGIN TRANSACTION
IF NOT EXISTS (SELECT Something FROM Somewhere WHERE Something = '123abc')
AND NOT EXISTS (SELECT Something FROM Somewhere WHERE Something = 'abc123')
SET @ReturnCode = 2
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
精彩评论