Last post Dec 16, 2010 04:09 PM by mndrs5
Jun 18, 2007 03:15 PM|penn433|LINK
I created a temp table in my stored procedure and then added a new identity column to it.
However, I am not able to use this new column immediately, it says column not found.
SELECT * INTO #temp FROM table_name
ALTER TABLE #temp ADD __Identity int IDENTITY(1,1)
SELECT * FROM #temp WHERE __Identity >= 10
Here __Identity column is not found. If I just did SELECT * FROM #temp without the where clause, the final result does have the __Identity column correctly added to the table. Why can't I query it?
Jun 18, 2007 04:09 PM|Motley|LINK
That is a good question. I originally had the problem, but then it just went away and I can't get it to come back again. From Management Studio, try placing a "GO" between each statement. That's what I did to get the problem to go away and can't get it
to come back again. Are you issuing these to SQL Server from 3 different command objects on the same connection?
Jun 19, 2007 11:06 AM|penn433|LINK
All these statements are in one stored procedure, so "GO" is not going to work. Thanks!
Jun 21, 2007 12:33 AM|Nai-Dong Jin - MSFT|LINK
Based on my understanding ,these three statements will be pre-checked at the same time, and then each statement will be executed. The third statement can't pass the check because the first two statements has't been executed.
I suggest you to write seperate these two SP, one is for creating a column, another is for query.
create proc CreateColumn
ALTER TABLE table ADD _identity int IDENTITY(1,1)
-- For the Creating Column
exec procfinal -- execute the CreateColumn SP.
create proc QueryResult
select * from table where _identity>'10'
Dec 16, 2010 04:09 PM|mndrs5|LINK
Hi,i have a same problem ..how did you solve that and if using two different sp,then please let me know that how did you merge those sps in one store procedure..