paging through records of dynamic tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

paging through records of dynamic tables

Author

Topic

cfausten
Starting Member

2 Posts

Posted - 2001-03-11 : 18:49:32

this question involves a stored procedure, designed to support paging through records of a large table, the name of which can be passed in as a variable.

I'll include it below, in hopes that someone will be able willing to assist. essentially, the following code segment:
.......
SET @SQL = 'SELECT COUNT(*) AS MoreRecords FROM '+@TempItems+' WHERE gridrow > ' SET @SQL = @SQL+@LastRec
EXEC(@SQL)
.......
produces the following error:
.......
Syntax error converting the varchar value 'SELECT COUNT(*) AS MoreRecords FROM #wtDCENTRAL WHERE gridrow > ' to a column of data type int.
.......
here's the entire procedure (it started out as a sample from the 4guysfromrolla website):
.......
CREATE PROCEDURE sp_PagedItems_wt_austen
(
@TableName varchar(100),
@Page int,
@RecsPerPage int
)
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

-- Find out where we will start our records from
DECLARE @RecCount int
SELECT @RecCount = @RecsPerPage * @Page + 1

--Create a temporary table and fill it with data
DECLARE @TempItems varchar(100)
DECLARE @SQL varchar(1000)
SET @TempItems = '#'+@TableName
SET @SQL = 'SELECT * INTO '+@TempItems+' FROM '+@TableName+' ORDER BY gridrow'
EXEC(@SQL)

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

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SET @SQL = 'SELECT COUNT(*) AS MoreRecords FROM '+@TempItems+' WHERE gridrow > ' SET @SQL = @SQL+@LastRec
EXEC(@SQL)

-- The following select statement is ultimately going to be replaced by the above dynamic SQL SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.gridrow > @LastRec
)
FROM #TempItems
WHERE gridrow > @FirstRec AND gridrow < @LastRec

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
.......
thanks! /a.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources