TSQL question - what am I doing wrong? 
Author Message
 TSQL question - what am I doing wrong?

Good Afternoon everyone,

I stumbled across this issue today, and am perplexed.  Haven't find anything
in Books Online about it yet.

I'm running SQL2000, and executing the following code thru query analyzer,
and it runs fine:

----------------------
USE Northwind
GO

SELECT CompanyName
INTO #tmp
FROM Customers

ALTER TABLE #tmp
ADD TestColumn int NULL

SELECT *
FROM #tmp
--WHERE TestColumn IS NULL

DROP TABLE #tmp
---------------------

With the comment, the last SELECT statement returns both the CompanyName and
TestColumn fields.  However, when I uncomment the WHERE clause, I get an
"Invalid Column Name" error.

Anyone know why this happens?  Thanks!

Jon Richt



Wed, 06 Jul 2005 03:08:48 GMT
 TSQL question - what am I doing wrong?

Jon

The problem is that the syntax check takes place before running any of the
statements.  At the time of the syntax check, there is no TestColumn.  If
you break this into 2 parts, and run the "SELECT * FROM #tmp WHERE
TestColumn IS NULL" separately it should work.

Joe



Wed, 06 Jul 2005 03:17:35 GMT
 TSQL question - what am I doing wrong?
I would think of it as another reason for not to use SELECT * in
your queries. Sure it looks a bit strange (call it a bug), however,
the correct way to do this is to add a GO after the ALTER TABLE
statement like:

ALTER TABLE #tmp
ADD TestColumn INT NULL
GO

Also if you use:

SELECT CompanyName, TestColumn
FROM #tmp
--WHERE TestColumn IS NULL

the compiler will catch it. I would like to hear other responses too.

--
- Anith
(Please respond only to newsgroups)



Wed, 06 Jul 2005 03:19:24 GMT
 TSQL question - what am I doing wrong?
QA is parsing the CREATE TABLE and your SELECT statement and saying, "hey,
wait a minute, there's no column with that name" - because the ALTER TABLE
is in the same batch, it doesn't see that as 'committed'...

So, in QA at least, you can add a GO after the ALTER TABLE statement, and it
should work fine.  Within a stored procedure, you can't use GO, so instead
of SELECT INTO and ALTER TABLE, the first option I can think of is that
you'll want to CREATE the #temp table beforehand, and create a valid INSERT
INTO ...SELECT statement.

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.


Quote:
> Good Afternoon everyone,

> I stumbled across this issue today, and am perplexed.  Haven't find
anything
> in Books Online about it yet.

> I'm running SQL2000, and executing the following code thru query analyzer,
> and it runs fine:

> ----------------------
> USE Northwind
> GO

> SELECT CompanyName
> INTO #tmp
> FROM Customers

> ALTER TABLE #tmp
> ADD TestColumn int NULL

> SELECT *
> FROM #tmp
> --WHERE TestColumn IS NULL

> DROP TABLE #tmp
> ---------------------

> With the comment, the last SELECT statement returns both the CompanyName
and
> TestColumn fields.  However, when I uncomment the WHERE clause, I get an
> "Invalid Column Name" error.

> Anyone know why this happens?  Thanks!

> Jon Richt




Wed, 06 Jul 2005 03:20:43 GMT
 TSQL question - what am I doing wrong?
thanks everyone,

the use of GO in QA solves the problem, so yes, it's a
"batching/compiling" issue (for lack of a better term).

It surprises me it's not possible in a sproc (ie. SELECT INTO and ALTER
TABLE, then referring to the column later).  Is there a way to change
when the procedure is "compiled"?  In other words, force SQL to not
check syntax at run time?  Or is there a way to commit the addition of
the column, so that referring to it later will work?

Sure, I know that explicitly creating the temp table will solve this.
Honestly, at this point it's more of an intellectual challenge than a
practical one.

But thanks for your time in any case,

Jon Richt

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Wed, 06 Jul 2005 03:40:03 GMT
 TSQL question - what am I doing wrong?

Jom,

Even if you would do something like adding WITH RECOMPILE when you create the proc, it won't
help. A proc is always compiled as a unit (even when the recompile, perhaps several times per
execution, is triggered through reasons inside the proc, like SET options etc).
But you can use dynamic SQL which will postpone even parsing until run-time:

EXEC('SELECT...')

There are considerations with dynamic SQL. I avoid it, mostly use it in system programming and
only when needed. See http://www.algonet.se/~sommar/ for more info.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

Quote:

> thanks everyone,

> the use of GO in QA solves the problem, so yes, it's a
> "batching/compiling" issue (for lack of a better term).

> It surprises me it's not possible in a sproc (ie. SELECT INTO and ALTER
> TABLE, then referring to the column later).  Is there a way to change
> when the procedure is "compiled"?  In other words, force SQL to not
> check syntax at run time?  Or is there a way to commit the addition of
> the column, so that referring to it later will work?

> Sure, I know that explicitly creating the temp table will solve this.
> Honestly, at this point it's more of an intellectual challenge than a
> practical one.

> But thanks for your time in any case,

> Jon Richt

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Wed, 06 Jul 2005 20:34:19 GMT
 TSQL question - what am I doing wrong?
Hi Tibor,
Your reply helped me tie a few key concepts together - thanks.  Will
play around with the EXECUTE statement (I'd forgotten about it until
now)...

Thanks again,

Jon Richt

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Fri, 08 Jul 2005 21:23:56 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. What am I forgetting or doing wrong

2. what am I doing wrong in this procedure?

3. what am i doing wrong?

4. Simple one what am i doing wrong

5. Darn what am i doing wrong?

6. What am I doing wrong

7. What am I doing wrong?

8. Joins - What I'am doing wrong

9. I think I am doing something wrong

10. what am I doing wrong?

11. What am I doing wrong...

12. What am I doing wrong with this SP??


 
Powered by phpBB® Forum Software