Can This Work?

  • demonfox (2/28/2013)


    I got it right, but it was a sheer guess on the mind of OP ..

    will this code execute successfully

    it should have been mentioned as the 4th step..

    also, it does execute ; so I guess the option should be like "Yes, but it will end up in an infinite loop."

    or does the OP mean by the sql server message "command executed successfully " :unsure:

    +1 It came to trying to understand the author's intent again... I hate these.

    It would create an infinite loop except the max recursion setting will kill it, so it's not infinite.

    And in either case, Yes it returns values... 32 times, at which point it hits max recursion and returns an error.

    None of the answers were perfectly correct, and it came down to determining the author's intent.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • ashwani24 (2/28/2013)


    I guess answer is Yes as it compiled and executed successfully but sql server restricts for nested looping to 32 level so it fails after execution of 32 times.

    +1 exactly... none of the answers were perfectly correct and two of them were sort of correct.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • ronmoses (3/1/2013)


    Without a BEGIN and END defining the body of proc sp1, I assumed it would only use the first statement, like an IF...THEN statement does. Then it would try to exec sp2 as a line separate from the SP definition - and since sp2 doesn't exist, it would throw an error.

    I'm not entirely clear why that wouldn't happen. If you don't use BEGIN and END in your proc definition, does it simply include every line of code that follows? At least until it hits a GO?

    ron

    Yes, BEGIN and END are not required to start or end a stored procedure.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Thank for the question,

    I am not sure the definitions for "Infinite loop" and "nesting" and "Recurring" in sql server.

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

  • Hugo Kornelis (3/1/2013)


    It is very easy to create infinite loops that will never terminate. For instance:

    DECLARE @i = 0;

    WHILE @i < 10;

    BEGIN

    SET @i = 1; -- Type, should have been SET @i += 1;

    END;

    Thank you Hugo... saved me having to write this sort of thing. 🙂



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Another of those awful questions where one has to guess at the intention of the author, because it isn't made at all clear, and in fact what looks like the intention on the face of it is of course not what was intended at all.

    Frankly, I'm fed up with questions like this. This asks if some code will run. If you attempt to run it, it doesn't run, it delivers a syntac error. The answer "syntax error" is wrong, it claims an infinite loop is generated. How far out of the real world are we expected to go to pick the "right" piece of nonsense as the answer? What's the point of a question where the "right" answer is so obviously completely wrong?

    At least this time Steve has xhanged the text and added GO separators so that the answer sort ofmakes sense. Last time when we were told to run something as a single batch (which of course generated a syntax error) no correction was offered, the multiple batch behavious was accepted as the correct answer. So maybe things are getting better.

    Tom

  • tom.w.brannon (3/1/2013)


    The code in the email does not have GO in it but the code in the Web page does so it is not clear what is being tested here. Yes they are mutually referencing procedures but as noted, the system stops on its own after 32 nestings. Not sure whether to call that a success or not.

    I agree with Tom. The code is different from one to the other and thus is either intentional deception or a mistake, take your pick. But as such, there are two questions being asked one in one place and one in the other. Although this is just how many bosses do business, I though that QOD was above this.

    Good questions, both of them. You should have answered them both and explained you reasoning.

    Not all gray hairs are Dinosaurs!

  • I agree with Tom as well.

    Here's the question and answers

    Will this code execute and return a value?

    1. You will receive a syntax error

    2. No. You cannot call from on SP to second SP

    3. No, this will create an infinite loop

    4. Yes

    1 - this is obviously wrong. The error that comes up is not a syntax error, but rather the server stomping your buggy code

    2- You most certainly can

    3. Technically true, the code represents an infinite loop, it doesn't run one though because the server squashes it. However, there are ways to write infinite loops (as Hugo demonstrated)

    4. Also technically true, you get values (empty result sets) until such a time as the max recursion property is hit.

    3 & 4 are both correct and incorrect.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • I agree with Tom that this was (yet) another of QotDs that test my ability to guess the author's intent, not my technical knowledge.

    Somendra, I do appreciate your effort; however, next time - and I hope there will be next one - you may wish to bounce your question off someone before you submit it.

  • I believe the right answer SHOULD be 4. Yes.

    Can this work?

    1. You will receive a syntax error - You don't receive a syntax error in SQL Server 2008 R2

    2. No. You cannot call from on SP to second SP - You CAN call an SP from an SP in SQL Server 2008 R2

    3. No, this will create an infinite loop - In theory it creates an infinite loop. In practice SQL Server stops after a nesting level of 32. So you get 32 runs before it stops.

    4. Yes - It compiles and runs before it hits its internal limit and stops.

  • Miles Neale (3/1/2013)


    The code is different from one to the other and thus is either intentional deception or a mistake, take your pick.

    Neither. When errors are corrected after a question goes live, Steve (the admin of the site) can go in and edit the question - but only on the website, not in mails that have already been sent.

    I can't check it, but I am 99% sure that the mail always includes a standard text mentioning this process and urging the reader to check for diffferences and go with the question in the website if there are any differences.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo - The text on the email reads as follows:

    Think you know the answer? Click here, and find out if you are right.

    This question is worth 1 point in this category: T-SQL. We keep track of your score to give you bragging rights against your peers.

    We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center

    The process does not appear to be mentioned, as you suggested it did. But from now on I will not trust the email only the online version. Thanks for the heads-up that might help not getting miffed when this happens again as it probably will.

    Not all gray hairs are Dinosaurs!

  • i think it will show syntax error bca in between the statements no GO statement

  • Miles Neale (3/1/2013)


    Hugo - The text on the email reads as follows:

    Think you know the answer? Click here, and find out if you are right.

    This question is worth 1 point in this category: T-SQL. We keep track of your score to give you bragging rights against your peers.

    We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center

    The process does not appear to be mentioned, as you suggested it did. But from now on I will not trust the email only the online version. Thanks for the heads-up that might help not getting miffed when this happens again as it probably will.

    It is useful to trust only the on-line version, but it isn't foolproof. There is likely to be some delay before the correction is made, so you may find yourself answering before that.

    Tom

  • You don't get the chance to answer in the email. When you click, the whole question (Edited or not) appears, along with the answers. If you don't re-read the question, I'm not sure what more we can do.

    I will award back points, because I do think this was poorly worded, but I thought it was interesting as well.

Viewing 15 posts - 46 through 60 (of 85 total)

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