SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Commenting in Dynamic query


Commenting in Dynamic query

Author
Message
Jamsheer
Jamsheer
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 Visits: 237
Comments posted to this topic are about the item Commenting in Dynamic query
Lokesh Vij
Lokesh Vij
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2470 Visits: 1599
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 = 0
SELECT * 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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Sreepathi1987
Sreepathi1987
SSC Eights!
SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)

Group: General Forum Members
Points: 829 Visits: 310
good Question.......................
tommyh
tommyh
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1994 Visits: 2000
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 :-D

/T
Koen Verbeeck
Koen Verbeeck
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35145 Visits: 13270
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 :-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
sknox
sknox
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3058 Visits: 2852
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. :-P

EDIT: Fixed incorrect punctuation.
Bex
Bex
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1379 Visits: 1713
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
Steve Hall
Steve Hall
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3454 Visits: 11698
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.

Steve Hall
Linkedin
Blog Site
honza.mf
honza.mf
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2139 Visits: 1323
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
derek.colley
derek.colley
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1196 Visits: 603
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 http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search