Using 'ALTER TABLE' column in WHERE clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using 'ALTER TABLE' column in WHERE clause

Author

Topic

groston
Yak Posting Veteran

61 Posts

Posted - 2005-12-15 : 17:26:46

Stumbled across something that doesn't make sense - maybe you can help.

In a stored procedure, I have the following (this is simplified)

CREATE TABLE #tbl1 (vq int, v2 int, v3 int)
ALTER TABLE #tbl1 ADD v4 int

INSERT #tbl1
EXEC mySP

SELECT *
FROM #tbl1, #tbl2
WHERE #tbl1.v3 = #tbl2.v3

This works just fine. However, what I really need is this:

CREATE TABLE #tbl1 (vq int, v2 int)
ALTER TABLE #tbl1 ADD v3 int
ALTER TABLE #tbl1 ADD v4 int

INSERT #tbl1
EXEC mySP

SELECT *
FROM #tbl1, #tbl2
WHERE #tbl1.v3 = #tbl2.v3

But, when I try this, I get an
Invalid column name 'v3'.
thrown on the WHERE clause.

Can you please explain why? Better, can you offer a solution? (Renaming the column is an option, but I do not think that that is supported.)

I am running SQL Server 2000 SP3.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-15 : 17:34:13

The reason you're getting the error is that the sql server doesn't take ALTER TABLE statements into consideration when the code is checked for syntax and the plan is prepared. So it doesn't know that new columns will have been added by the time they are used.

As far as solutions, it depends on what you're tying to accomplish. I guess the obvious question is, why not CREATE the table with all the columns?

Be One with the Optimizer
TG

groston
Yak Posting Veteran

61 Posts

Posted - 2005-12-15 : 17:42:01

TG,

What's strange is that the error is a run time error, not compile time.

To answer your other question, this code is at the tail end of a nasty SP that performs the same basic operation on three different, underlying tables. To make the output of this SP 'freindly', the column names should reflect the underlying table and not be generic.

Yes, I can replace my

SELECT *

with

SELECT v1 as var 1, v2 as var 2

etc., and have the desired result, but I was hoping for a 'cleaner' solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources