Commenting in Dynamic query
-
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
-
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 -
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)
-
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 '-'.
-
Too bad about the string length issue, as it was a really good question otherwise.
-
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] -
hmm strange error...
-
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.
-
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?
-
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 -
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)
-
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
-
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 -
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