August 4, 2012 at 12:40 pm
Raghavendra Mudugal (8/4/2012)
Hello; nice question indeed.:-).....
.....
I see now the size is increased by 5 chars in each and the result is "1,0,1,0".
But in the SQL 2000 the interpretation is different and it gives me all 1. (after increasing to var length to 50)
I couldn't find the difference for these 2 versions on why the EXECUTE is executed with different results.
Anyone tested this in 2012 and is this giving same result as 2008 R or 2000?
🙂
Back in the good old days of SQL 2000 maybe SQL Server obeyed the ancient American convention that the line terminating character was char(10) (ascii LineFeed) on it's own as terminating a line, or maybe it wasn't. Clearly (from what you say) it didn't treat CarriageReturn (Char(13)) on it's own as terminating a line. I never actually found out if either char(13) on its own or char(10) on its own would work, because I was used to the European convention that a line was ended with a CarriageReturn, some otional Nulls (char(00)), a LineFeed, and optionally some more Nulls.
The reason for the American convention was that LineFeed was what EBCDIC NewLine (hex(21)) was translated by IBM to when converting EBCDIC to ASCII. Despite IBMs efforts to force their in-house stuff into the ASCII standard in the early 60s, the ASCII standard never had a NewLine character; but some IBM people (by no means all) were going to make sure that ASCII's LineFeed was treated as NewLine anywhere they had any influence. People who understood ASCII used to hate that translation, and so did IBM people (not only those dealing with flying head devices - even EBCDIC used CarriageReturn and LineFeed characters to cope with such devices - in fact it was an IBM man who was one of the leading lights behind the ASCII standard). In Europe we mostly used CarriageReturn followed by LineFeed (char(10)) (and optionally one of more NULLs) to terminate a line, because both flying head devices (most pen-based printers, and quite a few others) and moving carriage devices needed time line up the printing position with the left magin (ie carry out a carriage return or a pen or head return depending which part had to be returned). ANSI followed ASCII, not IBM, and so did ISO, so this crazy convention of treating LineFeed as NewLine ought to have been lost long ago, but it wasn't. And now MS has extended the convention (at least in text representing T-SQL, and in SMS when providing a text display or results) to treat CarriageReturn as LineFeed too.
It's probably quite sensible for SQL Server (or SSMS) to accept any of CRLF, CR on its own, or LF on its own, and LFCR as separating lines of SQL. They've almost done this in SQL 2008 and SQL 2008 R2: when interpreting these things as text in query-results using results to text reveals that CRLF, CR, and LF are all treated as NewLine; but LFCR is treated as 2 newlines. This treatment of LFCR strikes me as irrational, but as this is all just convention it probably isn't important.
I would still recommend that everyone uses CRLF rather than just CR or just LF, because if you shove a file that is coded using only CR or only LF at a printer you may find it's a standards conformant printer so that in one case every line overprints the previous line and in the other every line begins one character further to the right than the previous line; neither of those outcomes would provide any legible outout.
Tom
August 4, 2012 at 3:08 pm
honza.mf (8/3/2012)
Unfortunately nobody will have the most interesting combination finished with "where Fld1 =0 --*/".
I guess you're wrong. This codes executes and returns 0 rows and no error.
SET @str4 = 'select*from #tmpTable--/*' + CHAR(13) + 'where Fld1=0 --*/'
August 4, 2012 at 3:29 pm
Am I overlooking something, or has nobody yet asked the really relevant question - why would anyone put comments in dynamic SQL? They should go in the non-dynamic part of the SQL, where the dynamic SQL is built.
(And, even more important - dynamic SQL should preferably not be used, unless unavoidable, and if used then only with sp_executesql)
August 5, 2012 at 6:44 am
Hugo Kornelis (8/4/2012)
Am I overlooking something, or has nobody yet asked the really relevant question - why would anyone put comments in dynamic SQL?
Yes, Tommyh raised that question on 3rd Aug.
They should go in the non-dynamic part of the SQL, where the dynamic SQL is built.
[/quiote]
It can be useful to include comments so that they are in the string if someone prints it out to look at. Usually it's just as useful to have them in the constructing code, but not always.
(And, even more important - dynamic SQL should preferably not be used, unless unavoidable, and if used then only with sp_executesql)
That seems bad advice to me. I can use EXEC to specify an execution context with lower privilege than the construction code, but I can't do that with sp_executesql; of course sp_execute allows me to use a parametrised string, but I can't do that with EXEC. So sometimes there is a valid reason to use sp_executesql instead of EXEC and sometimes there is a valid reason to use EXEC instead of sp_executesql.
Good advice would be something like "never construct a sting to be executed that includes string expressions not under your control, and subject to that always make the string be executed at the lowest possible privilege". With advice like that, in any case where the operation expressed by the string has parameters which are provided from outside the constructing code sp_executesql will be used; in any case where there are no such parameters and there is scope to reduce privilege EXC will be used instead of sp_executesql; and in case where neither of those aplies either can sensibly be used.
Personally I use EXEC in the "doesn't matter" because it's less typing. :satisfied:
Tom
August 6, 2012 at 12:54 am
Thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 6, 2012 at 1:12 am
Hugo Kornelis (8/4/2012)why would anyone put comments in dynamic SQL? They should go in the non-dynamic part of the SQL, where the dynamic SQL is built.
I'm guessing the question setter used dynamic SQL because it was the best way to make it clear there was *supposed* to be a carriage return in the middle of the line? If he'd just put a line break in the select text readers might have assumed that was a formatting issue and ignored it.
August 6, 2012 at 6:32 am
Steve Jones - SSC Editor (8/3/2012)
I've edited the question down since I think it was a good question. I didn't catch the typo (grrr, no more images).I'll award points back.
Thank you Steve
August 9, 2012 at 4:24 am
This one was really useful for me. Thanks.
August 10, 2012 at 2:07 am
L' Eomot Inversé (8/3/2012)
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.
There is sometimes good cause for including comments in dynamic SQL. Someone who comes along afterwards and is trying to fix a problem or do an enhancement can benefit from those comments (provided he's willing to print the SQL string so that he can read them).
edit - fix broken quote tag
Doesn't it makes more sense to put your comments as line(s) immediately above the creation of the dynamic SQL rather than in the dynamic SQL? OK, if someone copies your code for the dynamic SQL and doesn't take the comments you now have uncommented code, but then the person copying should add their own comments if they don't use yours.
Edited for spelling.
August 10, 2012 at 2:50 am
Hugo Kornelis (8/4/2012)
Am I overlooking something, or has nobody yet asked the really relevant question - why would anyone put comments in dynamic SQL? They should go in the non-dynamic part of the SQL, where the dynamic SQL is built.(And, even more important - dynamic SQL should preferably not be used, unless unavoidable, and if used then only with sp_executesql)
You are right if dynamic SQL is built by rules in code only.
If it is built using some metadata stored in database, it may be important to identify the parts the query is constructed from. I'm using similar technique (but the dynamic query is constructed in C# code so from the SQL's point of view is nondynamic).
August 10, 2012 at 4:49 am
marlon.seton (8/10/2012)
L' Eomot Inversé (8/3/2012)
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.
There is sometimes good cause for including comments in dynamic SQL. Someone who comes along afterwards and is trying to fix a problem or do an enhancement can benefit from those comments (provided he's willing to print the SQL string so that he can read them).
edit - fix broken quote tag
Doesn't it makes more sense to put your comments as line(s) immediately above the creation of the dynamic SQL rather than in the dynamic SQL? OK, if someone copies your code for the dynamic SQL and doesn't take the comments you now have uncommented code, but then the person copying should add their own comments if they don't use yours.
Edited for spelling.
Sometimes comments in the generating code are enough. But saying they always are is a bit like saying you never need comments in code because you can always put them in separate documentation, and in my view just as wrongas that statement.
And of course you always have to cover the case where the generating code is run once and throw away, while the generated code continues in use for ever.
Tom
Viewing 11 posts - 46 through 55 (of 55 total)
You must be logged in to reply to this topic. Login to reply