Comment disables code?

It seem that using the old "--" style comment screws code in the IDE under
certain circumstances. As an example, the code in example one should create
a temporary table, populate it with the current int ID from a table along
with an int IDENTITY column that will serve as the new ID, then display the
resulting temporary table. The code runs as expected without the -- Comment
line, but as soon as it is added the table no longer populates, and the
temporary table is no longer displayed. The same type thing seems to happen
when you place -- comments following values in a SELECT statement as in
example two.
I ran the code under both Query Analyzer and the new 2005 IDE and they both
exhibited thje same behavior, but I've used the -- comment style for years
without an issue so I'm baffled. So far it only seems to affect SELECT
statements.
Has anyone else encountered this?
/* Example One */
PRINT '*** Create the LotEntity ID mapping table';
IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'LotEntity')
DROP TABLE tempdb.dbo.LotEntity
CREATE TABLE tempdb.dbo.LotEntity(OldID int, NewID int IDENTITY)
-- Comment
INSERT tempdb.dbo.LotEntity (OldID)
SELECT EntityID
FROM dbo.tbl_LotEntities
SELECT * FROM tempdb.dbo.LotEntity
/* Example 2 */
INSERT TableTwo (
ColumnOne,
ColumnTwo)
SELECT
ValueOne, -- ColumnOne
ValueTwo
FROM TableTwoI couldn't reproduce either of those problems.
For example 1, the problem probably not the comment, but that you do the SEL
ECT in the INSERT in the
same batch as the table is created. My guess is that the parses gets confuse
d when you in the same
batch creates a table, and then does a SELECT inside an INSERT where both of
these later statements
refer to the same table. That code generated an error (probably from the SEL
ECT inside the INSERT)
regardless of whether I have the comment or not.
For example 1, I got a proper error message stating that the table doesn't e
xist. And if I create
the table, no errors:
DROP TABLE TableTwo
GO
CREATE TABLE TableTwo (ColumnOne int, ColumnTwo int, ValueOne int, ValueTwo
int)
GO
INSERT TableTwo (
ColumnOne,
ColumnTwo)
SELECT
ValueOne, -- ColumnOne
ValueTwo
FROM TableTwo
If you can post a full repro, we can have a look at it. Oh, watch out for ed
itors etc that doesn't
produce a full CRLF. I've seen cases when you have only a CR (or was it LF)
which visually produces
a new line, but doesn't end the comment.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Byron" <Byron@.discussions.microsoft.com> wrote in message
news:BF89D3A3-A798-4220-852F-7A5FCC33971E@.microsoft.com...
> It seem that using the old "--" style comment screws code in the IDE under
> certain circumstances. As an example, the code in example one should crea
te
> a temporary table, populate it with the current int ID from a table along
> with an int IDENTITY column that will serve as the new ID, then display th
e
> resulting temporary table. The code runs as expected without the -- Comme
nt
> line, but as soon as it is added the table no longer populates, and the
> temporary table is no longer displayed. The same type thing seems to happ
en
> when you place -- comments following values in a SELECT statement as in
> example two.
> I ran the code under both Query Analyzer and the new 2005 IDE and they bot
h
> exhibited thje same behavior, but I've used the -- comment style for years
> without an issue so I'm baffled. So far it only seems to affect SELECT
> statements.
> Has anyone else encountered this?
>
> /* Example One */
> PRINT '*** Create the LotEntity ID mapping table';
> IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
=
> 'LotEntity')
> DROP TABLE tempdb.dbo.LotEntity
> CREATE TABLE tempdb.dbo.LotEntity(OldID int, NewID int IDENTITY)
> -- Comment
> INSERT tempdb.dbo.LotEntity (OldID)
> SELECT EntityID
> FROM dbo.tbl_LotEntities
> SELECT * FROM tempdb.dbo.LotEntity
>
> /* Example 2 */
> INSERT TableTwo (
> ColumnOne,
> ColumnTwo)
> SELECT
> ValueOne, -- ColumnOne
> ValueTwo
> FROM TableTwo
>|||The example code stands alone, using a table valued variable in place of my
real table, but the results are the same. When Example 1 is run it correctl
y
retuns the two rows are expected. When Example 2 is run it does not.
Now the freaky part I just discovered while doing these examples. I can
copy the problematic code block including the comment out of my main query
into a new query window and it fails. I then copy it into Notepad then back
out into a query window and it works.
Some of the code was created using a stored procedure that built the SQL
strings based on table structures and I used CHAR(13) for CRLF to format the
code. Is it possible that the absence of CHAR(10) is confusing SQL? The
code was edited and saved in SQL Server Management Studio after it was
generated and the piece of code that is failing was not generated; it was
created by hand, but was added the file that had generated code in it .
/* Example 1 */
SET NOCOUNT ON
DECLARE @.t TABLE(ID int)
INSERT @.t VALUES(1)
INSERT @.t VALUES(2)
PRINT '*** Create the LotEntity ID mapping table';
IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'LotEntity')
DROP TABLE tempdb.dbo.LotEntity
CREATE TABLE tempdb.dbo.LotEntity(OldID int, NewID int IDENTITY)
INSERT tempdb.dbo.LotEntity (OldID)
SELECT ID
FROM @.t
SELECT * FROM tempdb.dbo.LotEntity
/* Example 1 results */
*** Create the LotEntity ID mapping table
OldID NewID
-- --
1 1
2 2
/* Example 2 */
SET NOCOUNT ON
DECLARE @.t TABLE(ID int)
INSERT @.t VALUES(1)
INSERT @.t VALUES(2)
PRINT '*** Create the LotEntity ID mapping table';
IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'LotEntity')
DROP TABLE tempdb.dbo.LotEntity
CREATE TABLE tempdb.dbo.LotEntity(OldID int, NewID int IDENTITY)
-- Comment
INSERT tempdb.dbo.LotEntity (OldID)
SELECT ID
FROM @.t
SELECT * FROM tempdb.dbo.LotEntity
/* Example 2 results */
*** Create the LotEntity ID mapping table
"Byron" wrote:

> It seem that using the old "--" style comment screws code in the IDE under
> certain circumstances. As an example, the code in example one should crea
te
> a temporary table, populate it with the current int ID from a table along
> with an int IDENTITY column that will serve as the new ID, then display th
e
> resulting temporary table. The code runs as expected without the -- Comme
nt
> line, but as soon as it is added the table no longer populates, and the
> temporary table is no longer displayed. The same type thing seems to happ
en
> when you place -- comments following values in a SELECT statement as in
> example two.
> I ran the code under both Query Analyzer and the new 2005 IDE and they bot
h
> exhibited thje same behavior, but I've used the -- comment style for years
> without an issue so I'm baffled. So far it only seems to affect SELECT
> statements.
> Has anyone else encountered this?
>
> /* Example One */
> PRINT '*** Create the LotEntity ID mapping table';
> IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
=
> 'LotEntity')
> DROP TABLE tempdb.dbo.LotEntity
> CREATE TABLE tempdb.dbo.LotEntity(OldID int, NewID int IDENTITY)
> -- Comment
> INSERT tempdb.dbo.LotEntity (OldID)
> SELECT EntityID
> FROM dbo.tbl_LotEntities
> SELECT * FROM tempdb.dbo.LotEntity
>
> /* Example 2 */
> INSERT TableTwo (
> ColumnOne,
> ColumnTwo)
> SELECT
> ValueOne, -- ColumnOne
> ValueTwo
> FROM TableTwo
>|||> Some of the code was created using a stored procedure that built the SQL
> strings based on table structures and I used CHAR(13) for CRLF to format t
he
> code. Is it possible that the absence of CHAR(10) is confusing SQL?
Most probably. Make sure you have CHAR(13) + CHAR(10) and you should be fine
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Byron" <Byron@.discussions.microsoft.com> wrote in message
news:ACFB2214-1B3A-4794-B5ED-FE8B55D0B167@.microsoft.com...
> The example code stands alone, using a table valued variable in place of m
y
> real table, but the results are the same. When Example 1 is run it correc
tly
> retuns the two rows are expected. When Example 2 is run it does not.
> Now the freaky part I just discovered while doing these examples. I can
> copy the problematic code block including the comment out of my main query
> into a new query window and it fails. I then copy it into Notepad then ba
ck
> out into a query window and it works.
> Some of the code was created using a stored procedure that built the SQL
> strings based on table structures and I used CHAR(13) for CRLF to format t
he
> code. Is it possible that the absence of CHAR(10) is confusing SQL? The
> code was edited and saved in SQL Server Management Studio after it was
> generated and the piece of code that is failing was not generated; it was
> created by hand, but was added the file that had generated code in it .
> /* Example 1 */
> SET NOCOUNT ON
> DECLARE @.t TABLE(ID int)
> INSERT @.t VALUES(1)
> INSERT @.t VALUES(2)
> PRINT '*** Create the LotEntity ID mapping table';
> IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
=
> 'LotEntity')
> DROP TABLE tempdb.dbo.LotEntity
> CREATE TABLE tempdb.dbo.LotEntity(OldID int, NewID int IDENTITY)
> INSERT tempdb.dbo.LotEntity (OldID)
> SELECT ID
> FROM @.t
> SELECT * FROM tempdb.dbo.LotEntity
> /* Example 1 results */
> *** Create the LotEntity ID mapping table
> OldID NewID
> -- --
> 1 1
> 2 2
>
> /* Example 2 */
> SET NOCOUNT ON
> DECLARE @.t TABLE(ID int)
> INSERT @.t VALUES(1)
> INSERT @.t VALUES(2)
> PRINT '*** Create the LotEntity ID mapping table';
> IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
=
> 'LotEntity')
> DROP TABLE tempdb.dbo.LotEntity
> CREATE TABLE tempdb.dbo.LotEntity(OldID int, NewID int IDENTITY)
> -- Comment
> INSERT tempdb.dbo.LotEntity (OldID)
> SELECT ID
> FROM @.t
> SELECT * FROM tempdb.dbo.LotEntity
> /* Example 2 results */
> *** Create the LotEntity ID mapping table
>
> "Byron" wrote:
>|||Probably a good Idea to store the CHAR(13) + CHAR(10) in a variable so you
are not having to do that many calls for every line of dynamic code you
create. Yes its subsecond but if you throw millions of records at the
procedure you could run into some issues with performance.
"Tibor Karaszi" wrote:

> Most probably. Make sure you have CHAR(13) + CHAR(10) and you should be fi
ne.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Byron" <Byron@.discussions.microsoft.com> wrote in message
> news:ACFB2214-1B3A-4794-B5ED-FE8B55D0B167@.microsoft.com...
>