alter table #TempTable problem

[pb648174]

I am trying to add a column to a temp table and then immeditaely query
against that new column. If I do this in Query Analyzer it works fine
as long as there is a go in between, but I can't use a go inside a
stored proc.. How do i get SQL to finish processing the alter table
command so I can use the new column?

alter table #TempPaging add TIId int not null identity
--go --fixes the problem in QA, but not in proc
Select * From #TempPaging Where TIId > 0 AND TIId < 11

(Error TIId does not exist)

[Erland Sommarskog]

Re: alter table #TempTable problem

pb648174 (google@webpaul .net) writes:[color=blue]
> I am trying to add a column to a temp table and then immeditaely query
> against that new column. If I do this in Query Analyzer it works fine
> as long as there is a go in between, but I can't use a go inside a
> stored proc.. How do i get SQL to finish processing the alter table
> command so I can use the new column?
>
> alter table #TempPaging add TIId int not null identity
> --go --fixes the problem in QA, but not in proc
> Select * From #TempPaging Where TIId > 0 AND TIId < 11[/color]

It would help if you provided the context you are trying to do
this in. The problem is that if the procedure is recompiled after
CREATE TABLE, but before ALTER TABLE, the refernce to the column
not yet created causes an error, as there is - luckily! - no deferred
name resolution on column names.

Thus some kind of workaround is needed, which is why I asked for
context.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[David Portas]

Re: alter table #TempTable problem

Why add a column to the temp table? Just create it with the right columns to
start with.

--
David Portas
SQL Server MVP
--

[pb648174]

Re: alter table #TempTable problem

Ok, here is what I am trying to do - make a generic routine for paging
that can be added to any proc and can be maintained easily rather than
copying and pasting in the same code all the time.

In proc 1 I have:

Select *
Into #TempPaging
from ScheduleTask

--do paging based on temp table
exec spDoPaging @itemsPerPage, @Page, @TotalPages OUTPUT, @TotalRecords
OUTPUT

In proc 2(spDoPaging I have:
Select @TotalRecords=C ount(*) From #TempPaging
Select @TotalPages=CEI LING(Cast(@Tota lRecords as
decimal)/Cast(@itemsPerP age as decimal))

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @itemsPerPage
SELECT @LastRec = (@Page * @itemsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT * FROM #TempPaging WHERE TIID > @FirstRec AND TIID < @LastRec

--clean up
drop Table #TempPaging



The kicker is that it all depends on the identity column so that I have
a row number for each item in the list, so that I can get the
appropriate page. so the below line is crucial

--add numbering
alter table #TempPaging add TIId int not null identity

Adding this line to proc1 works and is my workaround for now, but I
would like to have all the paging functionality in the spDoPaging if
possible and just use the "Into #TempPaging" and call to stored proc
anytime I want to add paging to a particular proc.

[David Portas]

Re: alter table #TempTable problem

You can put the IDENTITY function in the SELECT INTO statement, then
you don't have to add it later.

There are also other paging methods that don't require IDENTITY and
temp tables:

http://www.aspfaq.com/show.asp?id=2120

--
David Portas
SQL Server MVP
--

[David Portas]

Re: alter table #TempTable problem

> You can put the IDENTITY function in the SELECT INTO statement, then[color=blue]
> you don't have to add it later.[/color]

Example:

SELECT IDENTITY(INT, 1,1) AS tiid, *
INTO #TempPaging
FROM YourTable

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Re: alter table #TempTable problem

pb648174 (google@webpaul .net) writes:[color=blue]
> Ok, here is what I am trying to do - make a generic routine for paging
> that can be added to any proc and can be maintained easily rather than
> copying and pasting in the same code all the time.[/color]
[color=blue]
> -- Now, return the set of paged records, plus, an indiciation of we
> -- have more records or not!
> SELECT * FROM #TempPaging WHERE TIID > @FirstRec AND TIID < @LastRec[/color]

You could put this in dynamic SQL with sp_executesql.

However, I think you are in a dead end here. I would assume that
you expect the IDENTITY values to respect some sort of order that you
want the data to be presented in. Don't count on that.

The SELECT INTO with the IDENTITY() function suggested by David is a
somewhat better bet if the amount of data is small, and you use an
ORDER BY clause. But you are not guaranteed to get rows in order.

CREATE TABLE followed by INSERT is safer, not the least if you add
OPTION (MAXDOP 1) to avoid surprises with parallelism.

Of course, since you don't know exactly what the table would look
like, CREATE TABLE is kind of difficult, but a SELECT INTO with
an IDENTITY() and a WHERE condition of 1 = 0 could cut it.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[pb648174]

Re: alter table #TempTable problem

,IDENTITY(INT, 1,1) AS TIId --add numbering
Into #TempPaging

Ok, I changed my code to have this as the line(s) that I can copy and
paste into procs and then just put in the paging proc lines below that

--do paging based on temp table
exec spDoPaging @itemsPerPage, @Page, @TotalPages OUTPUT,
@TotalRecords OUTPUT

Which seems to work pretty well. Thanks for the help guys.