开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜