Commenting in Dynamic query
-
Comments posted to this topic are about the item Commenting in Dynamic query
-
Nice question. For all those who use dynamic sqls and sometime stuck up in this scenario, here is the tip for you.
CREATE TABLE #A(COL1 INT)INSERT INTO #A VALUES (1)
DECLARE @STR2 VARCHAR(100)
DECLARE @STR3 VARCHAR(100)
DECLARE @STR4 VARCHAR(100)
SET @STR1 = 'SELECT * FROM #A -- WHERE COL1 = 0'
SET @STR2 = 'SELECT * FROM #A --' + CHAR(13) + ' WHERE COL1 = 0'
SET @STR3 = 'SELECT * FROM #A /*' + CHAR(13) + ' WHERE COL1 = 0*/'
SET @STR4 = 'SELECT * FROM #A --/*' + CHAR(13) + ' WHERE COL1 = 0--*/'
PRINT @STR1
PRINT @STR2
PRINT @STR3
PRINT @STR4
Execute this, copy paste the result in query window. Check the result 🙂
SELECT * FROM #A -- WHERE COL1 = 0SELECT * FROM #A --
WHERE COL1 = 0
SELECT * FROM #A /*
WHERE COL1 = 0*/
SELECT * FROM #A --/*
WHERE COL1 = 0--*/
Hope this helps. I use this metod frequently when in trouble..
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
good Question.......................
-
Unless i wrote it off wrong... the answer is wrong.
create table #tmpTable (Fld1 char(1))insert into #tmpTable values ('1')
declare @str1 varchar(45)
declare @str2 varchar(45)
declare @str3 varchar(45)
declare @str4 varchar(45)
set @str1 = 'select * from #tmpTable -- where Fld1 = 0'
set @str2 = 'select * from #tmpTable --' + char(13) + 'where Fld1 = 0'
set @str3 = 'select * from #tmpTable /*' + char(13) + 'where Fld1 = 0 */'
set @str4 = 'select * from #tmpTable --/*' + char(13) + 'where Fld1 = 0 --*/'
execute (@str1)
execute (@str2)
execute (@str3)
execute (@str4)
drop table #tmpTable
Now @str4 is interesting. Because "0 --*/" produces one result and "0--/*" another.
declare @str4 varchar(45)set @str4 = 'select * from #tmpTable --/*' + char(13) + 'where Fld1 = 0 --*/'
print @str4
set @str4 = 'select * from #tmpTable --/*' + char(13) + 'where Fld1 = 0--*/'
print @str4
Gives a result of
select * from #tmpTable --/*
where Fld1 = 0 -
select * from #tmpTable --/*
where Fld1 = 0--
Now the question had a space between the 0 and -- and for me that produces an "Incorrect syntax near '-'." error.
Even more interesting is that it works differently in SQL2000. There all 4 work returning 1. 2005 and 2008R2 return the same result... personally though i like the 2000 result best 🙂
Now why on earth anyone would want to put comments in dynamic SQL is beyond me. Its hard enough getting all the little ' right and to introduce something into the string that doesnt need to be there... god no.
Offcourse this is all depending wether i wrote off the question correctly 😀
/T
-
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 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Of course, the real bug is this:
The string char(13) will break the line.
Char(13) is a Carriage Return, not a Line Feed. It should, therefore, move the cursor to the beginning of the current line and subsequent text should overwrite existing text on that line. Therefore all of the strings using char(13) without char(10) just before the where clause should become where clauses without select statements, and therefore return errors.
But Microsoft never played nice with ASCII control characters. 😛
EDIT: Fixed incorrect punctuation.
-
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 🙂
Got same result. Glad it's not just me!
Bex
-
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.I agree, it would have been such an interesting question if it had been tested correctly. As soon as I saw the length of the @STR fields I looked at the lengths of the queries, believing it to be yet another trick question.
Ah well, it isn't life or death - just irritatimg.
-
Oh, the variables are too short.
I guess author wrote and tested the question and before submitting he inserted some spaces.
So my response was wrong but I have my point.
See, understand, learn, try, use efficient
© Dr.Plch -
Answer is wrong, last query returns an error. Point refund please, my good sir!
---
Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me. -
I'm a bit baffled as to why the author chose 45 characters as the string length when the fourth one is longer than that...I actually wasn't sure what the result of the 4th one would be (because I wasn't sure whether -- would override the /*) so I ran it, but to save typing I named the table #tmp and thus it worked as the author apparently intended!
45 is an odd length all round, to be honest...wonder why it was chosen?
-
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 🙂
No, the last query returns 0 rows
How do you write the WHERE clause? ... with a heading space? 'where or ' where ?
Try this one code you'll be surprised:
DECLARE @STR1 VARCHAR(45)SET @STR1 = 'select * from #tmpTable --/*' + char(13) + 'where Fld1 = 0--*/'
print len(@STR1)
-
Carlo Romagnano (8/3/2012)
Try this one code you'll be surprised:
DECLARE @STR1 VARCHAR(45)SET @STR1 = 'select * from #tmpTable --/*' + char(13) + 'where Fld1 = 0--*/'
print len(@STR1)
All you've proved there is that a 45-character string can't ever be longer than 45 characters. Try adding PRINT @STR1 and you'll see that the string has lost the last two characters. Of course, that means it produces the same result in this particular instance, but that's a fluke--as already pointed out, if you put a space after the 0 then you'll get an error.
-
paul.knibbs (8/3/2012)
All you've proved there is that a 45-character string can't ever be longer than 45 characters. Try adding PRINT @STR1 and you'll see that the string has lost the last two characters. Of course, that means it produces the same result in this particular instance, but that's a fluke--as already pointed out, if you put a space after the 0 then you'll get an error.You are right: I missed the space after the ZERO.
So, now, I want my points back.
I don't like qotd where you should check each char.
-
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
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 15 posts - 1 through 15 (of 55 total)
You must be logged in to reply to this topic. Login to reply