August 3, 2012 at 2:50 am
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.
August 3, 2012 at 3:13 am
This was removed by the editor as SPAM
August 3, 2012 at 3:15 am
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
August 3, 2012 at 6:06 am
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)
August 3, 2012 at 6:53 am
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 '-'.
August 3, 2012 at 7:01 am
Too bad about the string length issue, as it was a really good question otherwise.
August 3, 2012 at 7:05 am
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.
August 3, 2012 at 7:07 am
hmm strange error...
August 3, 2012 at 7:26 am
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.
August 3, 2012 at 7:29 am
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?
August 3, 2012 at 7:31 am
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
August 3, 2012 at 8:39 am
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)
August 3, 2012 at 8:44 am
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
August 3, 2012 at 8:54 am
LadyRuna (8/3/2012)
The only way I could get it to return data was to change fromExecute @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
August 3, 2012 at 8:56 am
Thomas Abraham (8/3/2012)
LadyRuna (8/3/2012)
The only way I could get it to return data was to change fromExecute @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 55 total)
You must be logged in to reply to this topic. Login to reply