November 24, 2003 at 2:59 pm
I am attempting to break into PAGES a set of records from table 'table1'.
My idea is to copy table1 into #temptable, then create a new intetger column which numbers the records 1,2,3,4.....etc
That way, with this new column, I can slice out whichever page I want using the parameters that I pass in.
Below is my code.
The problem is I get back an error message:
Invalid column name 'TEMPCOL'.
The curious thing is, when i delete the line: WHERE TEMPCOL> @FirstRec AND TEMPCOL< @LastRec, the stored procedure will execute without error.
The other curious thing is that if I insert the line :
select column_name from tempdb.information_schema.columns where table_name like '#temptable%'
...then it shows that the column TEMPCOL does actually exist in #temptable.
Thanks in advance
------------------------------
CREATE Procedure pagewise
/*
this sp breaks records from a VIEW into pages
*/
(
@Page int = 1, --default to 1st page
@RecsPerPage int = 6 --default to 6 items per page
)
As
set nocount on
select
* into #temptable
from
dbo.table1
alter TABLE #temptable add TEMPCOL int identity (1,1) NOT NULL
--note: table1 originally did not have an identity column
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
SELECT * FROM #temptable
WHERE TEMPCOL> @FirstRec AND TEMPCOL< @LastRec
set nocount off
return
drop table #temptable
GO
Edited by - eamonroche on 11/24/2003 3:05:10 PM
November 24, 2003 at 10:16 pm
November 25, 2003 at 12:40 am
In addition to the above post, you might want to search this site for 'paging'.
Returns many useful hits.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply