Paging through Stored Procedure

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

General SQL Server Forums

New to SQL Server Programming

Paging through Stored Procedure

Author

Topic

mickyjtwin
Starting Member

12 Posts

Posted - 2005-11-14 : 17:32:51

I have a stored procedure which returns the results fine, i.e. my asp code of

do while not objRS.EOF
display record n
objRS.Movenext
Loop

Included in the stored procedure is the number of pages that are ultimately returned, however I don't know how to access that value from my asp code.

Here is the stored procedure. I want to get the value of PageCount from my ASP code????

CREATE PROCEDURE GetSortedPage(
@TableName VARCHAR(50),
@PrimaryKey VARCHAR(25),
@SortField VARCHAR(100),
@PageSize INT,
@PageIndex INT = 1,
@QueryFilter VARCHAR(100) = NULL
) AS
SET NOCOUNT ON

DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)

SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))

IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN

EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)

END
ELSE
BEGIN

EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)

END

RETURN 0
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-14 : 19:04:29

declare @PageCount int, @sql nvarchar(4000)
select @sql = 'SELECT @PageCount = (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName
exec sp_executesql @sql, N'@PageCount int out', @PageCount out

Then you can use @PageCount - an output parameter would probably be best.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mickyjtwin
Starting Member

12 Posts

Posted - 2005-11-14 : 20:08:14

Ok, so I have that entered, but how do I access that number from my ASP so I can calculate the total pages??
Thanks for the help,
Mick

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-14 : 20:23:06

Define it as an output parameter and it will be opoulated when the recordset has been transferred.
You could also transfer it as another column repeated on every row.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mickyjtwin
Starting Member

12 Posts

Posted - 2005-11-15 : 08:46:17

Ok, so I understand that I have to make it an output parameter, but not quite sure how to do that. As I understand it, I'm creating a variable @sql, which is a SELECT statement which contains the @PageCount variable which I want to output. Now, the exec line obviously executes the SELECT statement, but isn't this outputting @sql and @pagecount? The code for that is below, and the entire code as I have it at present is at the bottom,

Thanks,
Mick

declare @PageCount int, @sql nvarchar(4000)
select @sql = 'SELECT @PageCount = (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName
exec sp_executesql @sql, N'@PageCount int out', @PageCount out

*******************
CREATE PROCEDURE GetSortedPage(
@TableName VARCHAR(50),
@PrimaryKey VARCHAR(25),
@SortField VARCHAR(100),
@PageSize INT,
@PageIndex INT,
@QueryFilter VARCHAR(100) = NULL
) AS
SET NOCOUNT ON

DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)

SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))

IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN

EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)

declare @PageCount int, @sql nvarchar(4000)
select @sql = 'SELECT @PageCount = (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName
exec sp_executesql @sql, N'@PageCount int out', @PageCount out

END
ELSE
BEGIN

EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)

END

RETURN 0

mickyjtwin
Starting Member

12 Posts

Posted - 2005-11-15 : 09:16:48

Was getting error near AS statement, so changed to this...

declare @sqltemp nvarchar(100)
declare @PageCount int
set @sqltemp = 'SELECT @PageCount = (COUNT(*) - 1)/' + @SizeString + ' + 1 FROM ' + @TableName

exec sp_executeSql @sqltemp , N'@PageCount int output', @PageCount output

This can't get to output.
Tried referencing it by objRS("PageCount") but no ordinance is found. Seemed to easy at the time!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources