Posted by Chris Hohmann on 09/19/05 19:24

"Steve" <steve@hello.com> wrote in message news:432ecddf_2@x-privat.org...
>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
>
>

The WHERE and ORDER BY clauses should be:

WHERE utbl7.fld5 >= @fld1val
OR (utbl7.fld5 = @fld1val AND utbl7.Unique_ID > @uniqueid)
ORDER BY utbl7.fld5, utbl7.Unique_ID

[Back to original message]