Commenting in Dynamic query

  • vk-kirov

    SSCertifiable

    Points: 7686

    I got a point for choosing the "correct" answer because I hadn't counted characters in the dynamic queries. Now I want an extra point for that reason. This is why I'm posting this.

  • This was removed by the editor as SPAM

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    vk-kirov (8/3/2012)


    I got a point for choosing the "correct" answer because I hadn't counted characters in the dynamic queries. Now I want an extra point for that reason. This is why I'm posting this.

    LOL 😀

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

  • elena kos

    Ten Centuries

    Points: 1082

    I have also got the answer wrong, though ran the script in SSMS 🙁

    You can include the simple check of the characters number: select len(@string4)

  • Mike Hays

    SSCommitted

    Points: 1871

    Yeaaa, I am in agreement with tommyh

    There is a type-o in the question that produces a completely different result.

    Query Messages:

    --------------------------------

    (1 row(s) affected)

    (1 row(s) affected)

    (0 row(s) affected)

    (1 row(s) affected)

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '-'.

  • sestell1

    SSChampion

    Points: 10230

    Too bad about the string length issue, as it was a really good question otherwise.

  • mtassin

    SSC-Insane

    Points: 23096

    Koen Verbeeck (8/3/2012)


    Indeed, the correct answer is 1,0,1,error, because the last query produces a syntax error. Shame, because it was a pretty good question otherwise.

    Points back please 🙂

    +1 I agree.... I wound up guessing because of the syntax error for the last one.



    --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]

  • thadeushuck

    Ten Centuries

    Points: 1367

    hmm strange error...

  • Mike Is Here

    Hall of Fame

    Points: 3348

    derek.colley (8/3/2012)


    Answer is wrong, last query returns an error. Point refund please, my good sir!

    i agree the last statement evaluates to

    select * from #tmpTable --/*

    where Fld1 = 0 -

    I even counted off the length of each string because I thought that was the issue of the question.

  • (Bob Brown)

    SSCrazy

    Points: 2705

    Hmm. I executed the code and got:

    (1 row(s) affected)

    (0 row(s) affected)

    (1 row(s) affected)

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '-'.

    Looks like 1,0,1,error.

    What is the concensus to others running the code?

  • Thomas Abraham

    SSChampion

    Points: 10761

    Koen Verbeeck (8/3/2012)


    Indeed, the correct answer is 1,0,1,error, because the last query produces a syntax error. Shame, because it was a pretty good question otherwise.

    Points back please 🙂

    Picked my answer, then tested it. Funny thing is, without thinking too much about it, I made one change to the code: set the varchar() def to 100 characters (much like Lokesh Vij did in his code. So never saw the syntax error. Didn't bother me, as I got the same results I expected from visual inspection. THere's a lesson or two to be learned in there for me. 🙂

    Thanks for the question, in spite of the tiny flaw. Points for everyone on Steve!

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • LadyRuna

    SSCrazy

    Points: 2174

    Am I using an odd version of SQL?

    My results were:

    (1 row(s) affected)

    Msg 2812, Level 16, State 62, Line 14

    Could not find stored procedure 'select * from #tmp -- where fl = 0'.

    Msg 2812, Level 16, State 62, Line 15

    Could not find stored procedure 'select * from #tmp --

    where fl = 0'.

    Msg 2812, Level 16, State 62, Line 16

    Could not find stored procedure 'select * from #tmp /*

    where fl = 0 */'.

    Msg 2812, Level 16, State 62, Line 17

    Could not find stored procedure 'select * from #tmp --/*

    where fl = 0 --*/'.

    The only way I could get it to return data was to change from

    Execute @str1

    to

    execute sp_Executesql @str1

    so i picked the "all errors" result since that's what I got. Used SQL 2008 R2 (SP2)

  • TomThomson

    SSC Guru

    Points: 104772

    To me it looks like a good question, right answer, nothing wrong anywher, no error messages.

    I suppose those who went before will ask why. 😉

    Well, it's because I don't believe that a 45 character string can contain 46 characters. The string

    'select * from #tmpTable --/*'+ char(13) + ' where Fld1 = 0 --*/'

    contains 49 characters, so unless someone has changed the question since the previous posters complained about lengths those posters are all having some trouble counting. The assignment to a varchar(45) variable truncates the last 4 characters, not just the last 3, so the truncated string ends in '0 ', not in '0 -', resulting in a total absence of syntax error.

    I looked at the question and picked my answer without worrying about lengths, which I suppose was careless. So I got the point. But when I saw the numerous posts about it I decided I'd count and see if the complaints were right. As far as I can see no-one has any grounds for complaint - neither any who miscounted nor any who mistyped and got the same result as would miscounting. And now I get another point for pointing out that people can't count. Lucky me! 😀

    Tom

  • Thomas Abraham

    SSChampion

    Points: 10761

    LadyRuna (8/3/2012)


    The only way I could get it to return data was to change from

    Execute @str1

    to

    execute sp_Executesql @str1

    I believe that Execute (@str1) is what you should use. I note no parenthesis in your example.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Thomas Abraham

    SSChampion

    Points: 10761

    Thomas Abraham (8/3/2012)


    LadyRuna (8/3/2012)


    The only way I could get it to return data was to change from

    Execute @str1

    to

    execute sp_Executesql @str1

    I believe that Execute (@str1) is what you should use. I note no parentheses in your example.

    Added: When you don't use parentheses, the sting is interpreted as the name of a stored procedure, as you can see in the error messages you received.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

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

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