I have implemented the Stored Procedure "RowCount" method of controlling
paged recordsets in ASP as shown in this page
http://www.aspfaq.com/show.asp?id=2120.
I have had to make heavy alterations to the code in order for it to work
with my application.
It all worked fine until I tried to sort my data by a field which contained
a NULL value (even in just one record). Before I added additional criteria
for the Unique_ID field the stored procedure returned zero records when
sorting by such a field. I then added the Unique_ID criteria to the ORDER BY
and WHERE clauses (as seen below) and it excludes records.
In short obviously what I want this to do is order by fields that contain
NULL values (e.g. fld4 in sample data below). It currently pages and orders
perfectly in fields which have data in every row.
If anyone reads this and helps me, thanks *very* much in advance.
(P.S. sorry about the horrible state of my code but I've been messing with
it trying to get this fixed for ages)
(P.P.S the reason for passing in the long SQL string as a parameter in the
SP is because the string is generated using complicated loops and I found
this much easier in ASP than doing it in SQL)
******************************************
Here is an example of a call to the procedure from ASP:
EXEC st_paging_rowcount 7, 1, 50, 'SET ROWCOUNT 50 SELECT utbl7.*,
utbl6_1.fld1 AS fld3_Name FROM utbl7 LEFT OUTER JOIN utbl6 utbl6_1 ON
utbl7.fld3 = utbl6_1.Unique_ID WHERE utbl7.fld5 >= @fld1val OR
utbl7.Unique_ID >= @uniqueid ORDER BY utbl7.fld5',5
******************************************
Here is the SQL SP:
CREATE PROCEDURE st_paging_rowcount
@tableid INT,
@pagenum INT = 1,
@perpage INT = 50,
@finalselect NVARCHAR(1500),
@sortfield INT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ubound INT,
@lbound INT,
@pages INT,
@rows INT,
@querystring1 NVARCHAR(200),
@querystring2 NVARCHAR(200),
@querystring3 NVARCHAR(200)
SELECT @querystring1 = N'SELECT @rows = COUNT(*),
@pages = COUNT(*) / @perpage
FROM utbl' + CAST(@tableid AS NVARCHAR(15)) + ' WITH (NOLOCK)'
--SELECT GOGOGO = @querystring1
EXEC sp_executesql
@querystring1,
N'@rows INT OUTPUT, @pages INT OUTPUT, @perpage INT',
@rows OUTPUT, @pages OUTPUT, @perpage
IF @rows % @perpage != 0 SET @pages = @pages + 1
IF @pagenum < 1 SET @pagenum = 1
IF @pagenum > @pages SET @pagenum = @pages
SET @ubound = @perpage * @pagenum
SET @lbound = @ubound - (@perpage - 1)
SELECT
CurrentPage = @pagenum,
TotalPages = @pages,
TotalRows = @rows
-- this method determines the string values
-- for the first desired row, then sets the
-- rowcount to get it, plus the next n rows
DECLARE @fld1val NVARCHAR(64)
DECLARE @uniqueid INT
SELECT @querystring2 = N'SET ROWCOUNT ' + CAST(@lbound AS NVARCHAR(15))
+ '
SELECT
@fld1val = fld' + CAST(@sortfield AS NVARCHAR(15)) + ', @uniqueid
= Unique_ID
FROM
utbl' + CAST(@tableid AS NVARCHAR(15)) + ' WITH (NOLOCK)
ORDER BY
fld' + CAST(@sortfield AS NVARCHAR(15)) +', Unique_ID '
SELECT test0 = @querystring2
EXEC sp_executesql
@querystring2,
N'@fld1val NVARCHAR(64) OUTPUT, @uniqueid INT OUTPUT',
@fld1val OUTPUT, @uniqueid OUTPUT
--SELECT test = @finalselect
EXEC sp_executesql
@finalselect,
N'@fld1val NVARCHAR(64), @uniqueid INT',
@fld1val, @uniqueid
SET ROWCOUNT 0
END
*********************************************
Here is some sample data from the table (e.g. would like to sort by fld4):
Unique_ID Date_Added Who_Added Locked fld1
fld2 fld3 fld4
----------- --------------------------- ----------- ----------- ------------------------------
------------------------------
3 2005-09-16 16:12:30.200 1 0 Smith
John 1 NULL
4 2005-09-16 16:12:41.013 1 0 Jones
Chris 1 NULL
6 2005-09-16 16:13:10.187 1 0 Stamov
Stilian 1 NULL
7 2005-09-16 16:19:15.437 1 0 Lewicki
Steve 1 Colchester
8 2005-09-16 16:19:36.937 1 0 James
Phil 1 NULL
9 2005-09-16 16:20:35.327 1 0 Leroy
Didier 1 NULL
