pb648174 (go****@webpaul.net) writes:
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.
-- 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
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,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp