ALTER temp table - unexpected behaviour

Funny little problem with a temp table created by an sp and then
altered within the scope of the same sp. It doesn't seem to work when I
try it.
Here's some sample code;
/**** start create code ***/
use northwind
create procedure dbo.uspTest as
select top 5 productname
into #myTemp
from products
order by productname
alter table #myTemp add RowID int identity(1,1)
select * from #myTemp
GO
/**** end code ***/
If this is executed you get;
ProductName RowID
--
Alice Mutton 1
Aniseed Syrup 2
Boston Crab Meat 3
Camembert Pierrot 4
Carnavon Tigers 5
Now try this;
/**** start alter code ***/
use northwind
alter procedure dbo.uspTest as
select top 5 productname
into #myTemp
from products
order by productname
alter table #myTemp add RowID int identity(1,1)
select * from #myTemp where RowID = 1 --added where clause
GO
/**** end code ***/
When executed you get;
Result:
Error "Invalid column name 'RowID'"
So RowID is returned as part of the result set but if you try to add a
where clause on it, it doesn't exist? The same is true for inserts (if
the column being added is not an identity column).
I worked around the problem by creating the temp table with the
identity column first and then doing an insert but I'm curious about
the error. What did I miss?
Thanks.This is by design. At compile time, the engine has to validate the existence
of the columns. At this time, the alter statement is not yet committed,
thus, the error on compilation for the select statement.
You should change your proc to include the identity column as part of your
select/into.
e.g.
create procedure dbo.uspTest as
select top 5 productname,RowID=identity(int,1,1)
into #myTemp
from products
order by productname
select * from #myTemp
where RowID = 1 --added where clause
go
-oj
"Wolf" <spamcatcher5050@.hotmail.com> wrote in message
news:1124249380.861639.272680@.g14g2000cwa.googlegroups.com...
> Funny little problem with a temp table created by an sp and then
> altered within the scope of the same sp. It doesn't seem to work when I
> try it.
> Here's some sample code;
>
> /**** start create code ***/
> use northwind
> create procedure dbo.uspTest as
> select top 5 productname
> into #myTemp
> from products
> order by productname
> alter table #myTemp add RowID int identity(1,1)
> select * from #myTemp
> GO
> /**** end code ***/
> If this is executed you get;
> ProductName RowID
> --
> Alice Mutton 1
> Aniseed Syrup 2
> Boston Crab Meat 3
> Camembert Pierrot 4
> Carnavon Tigers 5
> Now try this;
> /**** start alter code ***/
> use northwind
> alter procedure dbo.uspTest as
> select top 5 productname
> into #myTemp
> from products
> order by productname
> alter table #myTemp add RowID int identity(1,1)
> select * from #myTemp where RowID = 1 --added where clause
> GO
> /**** end code ***/
> When executed you get;
> Result:
> Error "Invalid column name 'RowID'"
> So RowID is returned as part of the result set but if you try to add a
> where clause on it, it doesn't exist? The same is true for inserts (if
> the column being added is not an identity column).
> I worked around the problem by creating the temp table with the
> identity column first and then doing an insert but I'm curious about
> the error. What did I miss?
> Thanks.
>|||Awesome. This is going to save me so much time.
Thanks oj.|||You should note that the ORDER BY clause may effectively be ignored by the
server. In particular there is no guarantee that the IDENTITY values will be
assigned in Productname order. Don't use ORDER BY on SELECT INTO or
INSERT... SELECT.
David Portas
SQL Server MVP
--