Commenting in Dynamic query
-
You missed the parenthesis surrounding the (@str1)
-
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.If you had counted them (and counted correctly) you would have dropped 4 characters and got the same answer as you got without counting. The question appears to be perfectly OK, all the complaints are from people who either can't count or are not good copy typists (hey didn't type the string in the question, they typed something with a different length). :hehe:
Tom
-
When I change the size for @str4 from 45 to 50 and ran a print LEN(@str4) I got a 48 not 49, which is the number of characters I count manually twice, this leaves a single - at the end which results in a syntax error. The execute does in fact work for a varchar length of 48 or greater. As stated before 45 is a really strange length.
-
tommyh (8/3/2012)
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 --*/'
You typed it wrong. You typed 'where' instead of ' where' in three lines. It only matters in the last one of those.
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
Tom
-
Lon-860191 (8/3/2012)
When I change the size for @str4 from 45 to 50 and ran a print LEN(@str4) I got a 48 not 49, which is the number of characters I count manually twice, this leaves a single - at the end which results in a syntax error. The execute does in fact work for a varchar length of 48 or greater. As stated before 45 is a really strange length.Are you sure that you had the correct string? Or did you make the same error as Tommyh (who, being perhaps less arrogant that some others, published the string he had typed and said the question was only wrong if he's typed it right). He had missed the space before "where". Missing that does indeed give you 48, but the space was in the question so 48 was not the correct count. Perhaps you too omitted that space?
You'll see that in my first post I included the string I had counted (and also checked using LEN, since so many people were getting a shorter string), which was, I believe (having checked very carefully), the string expression in the question. If it isn't, people can point out where the difference between teh expression in the question and the expression in my post is, and I will learn from that.
Tom
-
It's hard to tell when the SQL script is an image.
I'm sure this will come down to some kind of argument, but when I see the image
I don't see the space before the where clause.
Because
' where'or 'where'both don't look exactly like that where clause, at least to me.
To me there doesn't appear to be a space between the ' and the word where.
--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] -
I don't see it either and that is what causes the error.
-
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.
-
The fourth statement is 48 characters long, hence when stored in a varchar(45) variable, it is truncated at character 45 resulting in a syntax error due to the last character being a '-'.
The correct answer is 1,0,1,error
-
Well I got it right but there is a problem with the edited version. On the page in the email I received this morning the sql has statements like
Set @str1 = 'select * from #tmpTable -- where Fld1 = 0’
And the SQL in the page where we answer says
SET @str1 = 'select count(*) from #tmpTable -- where Fld1 =0'
A bit confusing since I looked at the first set I saw in the daily email and then clicked to answer and it was different. Knowing that this can be tricky I reconsidered and did get it but really had to look close for a Friday. Thanks for making us think.
M.
Not all gray hairs are Dinosaurs!
-
mtassin (8/3/2012)
It's hard to tell when the SQL script is an image.I'm sure this will come down to some kind of argument, but when I see the image
I don't see the space before the where clause.
Because
' where'or 'where'both don't look exactly like that where clause, at least to me.
To me there doesn't appear to be a space between the ' and the word where.
You could be right. I see a space there, but on more careful study of the image I'm far from sure. :ermm:
In fact (looking at the relative widths of w,n,and l in that image it's clear that it's a fixed spacing font, so I've made a thorough fool of myself - if it's in a fixed spacing font, there isn't any space! :blush: And SSMS uses a fixed spacing font for the query window, so I should have expected it to be a fixed spacing font :blush: :blush: because the question was probably put together using an SSMS query window.
Apologies to all those I've accused of miscounting or miscopying - it's me that counted & copied wrong, not you. :blush: :blush: :blush: :blush: :blush:
Tom
-
For what this question was intending to do, it was a good question. Too bad about the string length issue causing controversy.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
L' Eomot Inversé (8/3/2012)
You could be right. I see a space there, but on more careful study of the image I'm far from sure. :ermm:In fact (looking at the relative widths of w,n,and l in that image it's clear that it's a fixed spacing font, so I've made a thorough fool of myself - if it's in a fixed spacing font, there isn't any space! :blush: And SSMS uses a fixed spacing font for the query window, so I should have expected it to be a fixed spacing font :blush: :blush: because the question was probably put together using an SSMS query window.
Apologies to all those I've accused of miscounting or miscopying - it's me that counted & copied wrong, not you. :blush: :blush: :blush: :blush: :blush:
The worst thing with this question is that one can make more variants of spaces. Trimming the string leads to several ending variants:
where Fld1 =0
where Fld1 =0 -
where Fld1 =0 --
First and third variants are OK, second is bad.
Unfortunately nobody will have the most interesting combination finished with "where Fld1 =0 --*/".
Why not use in such a question varchar(max) variables? It eliminates SQL Server 2000, but it's clear nobody has to count characters.
See, understand, learn, try, use efficient
© Dr.Plch -
Hello; nice question indeed.:-)
The below code which I have copied/written from the QotD email which I received today and this gives me the result as "1,error error, error" due to the variable length (under SQL2K8 R2)
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 count(*) from #tmpTable -- where Fld1 =0'SET @str2 = 'select count(*) from #tmpTable --' + CHAR(13) + 'where Fld1 =0'SET @str3 = 'select count(*) from #tmpTable /*' + CHAR(13) + 'where Fld1 =0 */'SET @str4 = 'select count(*) from #tmpTable --/*' + CHAR(13) + 'where Fld1 =0 --*/'EXECUTE(@str1)EXECUTE(@str2)EXECUTE(@str3)EXECUTE(@str4)DROP TABLE #tmpTableI 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?
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
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
Viewing 15 posts - 31 through 45 (of 55 total)
You must be logged in to reply to this topic. Login to reply