Try Catch Throw

  • glen.putnam

    Old Hand

    Points: 337

    I ran dbcc opentran after running this query.

    I receive a no active open transactions message. If the transaction is still open why did I get this message?

  • webrunner

    SSC-Dedicated

    Points: 30095

    Thanks, interesting question.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • SQL_Hunt

    SSC-Dedicated

    Points: 33283

    The options provided were little confusing, I mean those were framed in such a manner that one fail to respond. However, I did it properly. Scored 2 marks!

    Thanks.

  • Revenant

    SSC-Forever

    Points: 42467

    Very nice question - thank you, Justin!

  • DataTherapist

    Mr or Mrs. 500

    Points: 510

    Ah, but I feel that all of your comments are missing the point of the question.

    I read it to mean 'pick one answer', because it didn't say 'pick two' or 'choose all that apply'

    It succeeded in tricking me, because I wasn't aware that I would get 'compilation' errors in addition to the output of the proceedure.

    So just to check that my thinking is correct......

    The TRY starts... and fails because it can't insert a row into a non-existant table

    The TRY ends, and passes control to the CATCH.

    Question: Is the @@TRANCOUNT > 0 at this point? And if it is, is it because the BEGIN TRANSACTION inside the TRY incremented it - and the COMMIT didn't decrement it because it failed.. never got to the COMMIT inside the TRY?

    Either way, is it correct to think that: If the @@TRANCOUNT was greater than 0, then it would rollback, and if it was zero, then there is nothing to rollback, so either way it passes by those 3 lines with no errors, yes/no?

    In my thinking, in would have gotten to the 'THROW 50000.." line...

    but you say that it doesn't, that it does "Msg 266, Level 16, State 2 etc. "

    ARE BOTH messages generated by the statement-level recomplilation?

    Message 1= the table doesn't exist

    and

    Message 2= since the table didn't exist, I bombed out, and couldn't read the whole proceedure, therefore I have a mismatch of BEGIN / END or BEGIN/COMMIT....

    ??

    Love it.

  • TomThomson

    SSC Guru

    Points: 104772

    Hany Helmy (5/26/2015)


    OK, the author should have (like all other QotD) mention to select 2 answers, still it`s a very good question.

    Thanx.

    when I write questions I don't say how many answers to select; but Steve has always added it. Maybe there's been a change in policy?

    Tom

  • TomThomson

    SSC Guru

    Points: 104772

    DataTherapist (5/26/2015)


    Ah, but I feel that all of your comments are missing the point of the question.

    I read it to mean 'pick one answer', because it didn't say 'pick two' or 'choose all that apply'

    It succeeded in tricking me, because I wasn't aware that I would get 'compilation' errors in addition to the output of the proceedure.

    So just to check that my thinking is correct......

    The TRY starts... and fails because it can't insert a row into a non-existant table

    The TRY ends, and passes control to the CATCH.

    Evidently you didn't read the explanation. Control is not passed to the catch block for this type of error when it occurs in code at the same level (in terms of nesting of execution blocks) as the try-catch. So the error in the select statement is hadled as if it wasn't in the try block and you get the standard error handling instead of the catch block.

    Question: Is the @@TRANCOUNT > 0 at this point? And if it is, is it because the BEGIN TRANSACTION inside the TRY incremented it - and the COMMIT didn't decrement it because it failed.. never got to the COMMIT inside the TRY?

    Since execution never gets to that point (passes control to the CATCH block, it makes no sense to ask for the value of something at that point.

    If XACT_ABORT is OFF (the default) the error in the select statement aborts the batch (no further statements in the batch are executed) but doesn't roll the transaction back, so when the batch exits trancount is still 1.

    if XACT_ABORT is ON, the error in the select statement rolls back transactions to make trancount 0 before aborting the batch.

    Tom

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Tom257

    SSChampion

    Points: 11475

    Am I out of date? I am the only one who missing something here?

    Msg 102, Level 15, State 1, Procedure MyProc, Line 32

    Incorrect syntax near 'THROW'.

    Msg 2812, Level 16, State 62, Line 35

    Could not find stored procedure 'dbo.MyProc'.

    Whenever I have used TRY CATCH before, I've used RAISERROR.

    Well yes. I am out of date. The new job has me demoted 🙁 -

    SELECT @@VERSION

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

    Jun 17 2011 00:54:03

    Copyright (c) Microsoft Corporation

    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    Should have attempted the question on my home machine. 😀

  • michal.lisinski

    SSCertifiable

    Points: 6933

    Hi

    The THROW is a new feature in SQL Server 2012 and thus it's not available in 2008 R2 yet.

    Regards

    Mike

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Good question, thanks.

  • DataTherapist

    Mr or Mrs. 500

    Points: 510

    TomThomson (5/26/2015)


    DataTherapist (5/26/2015)


    Ah, but I feel that all of your comments are missing the point of the question.

    I read it to mean 'pick one answer', because it didn't say 'pick two' or 'choose all that apply'

    It succeeded in tricking me, because I wasn't aware that I would get 'compilation' errors in addition to the output of the proceedure.

    So just to check that my thinking is correct......

    The TRY starts... and fails because it can't insert a row into a non-existant table

    The TRY ends, and passes control to the CATCH.

    Evidently you didn't read the explanation. Control is not passed to the catch block for this type of error when it occurs in code at the same level (in terms of nesting of execution blocks) as the try-catch. So the error in the select statement is hadled as if it wasn't in the try block and you get the standard error handling instead of the catch block.

    Question: Is the @@TRANCOUNT > 0 at this point? And if it is, is it because the BEGIN TRANSACTION inside the TRY incremented it - and the COMMIT didn't decrement it because it failed.. never got to the COMMIT inside the TRY?

    Since execution never gets to that point (passes control to the CATCH block, it makes no sense to ask for the value of something at that point.

    If XACT_ABORT is OFF (the default) the error in the select statement aborts the batch (no further statements in the batch are executed) but doesn't roll the transaction back, so when the batch exits trancount is still 1.

    if XACT_ABORT is ON, the error in the select statement rolls back transactions to make trancount 0 before aborting the batch.

    Thanks Tom for taking time to reply.

    Peter A.R. Johnson

  • Bhushan Kulkarni

    SSCrazy

    Points: 2829

    Good question

  • stephen.long.1

    SSCrazy

    Points: 2571

    Great question, Justin, thanks!

  • mannat

    SSC Rookie

    Points: 35

    Why am i getting these two errors on executing?

    Msg 102, Level 15, State 1, Procedure MyProc, Line 16

    Incorrect syntax near 'THROW'.

    Msg 2812, Level 16, State 62, Line 1

    Could not find stored procedure 'dbo.MyProc'.

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply