Posted by Joe on 09/19/05 21:01

"Chris Hohmann" <nospam@thankyou.com> wrote in message
news:OlyvseTvFHA.3080@tk2msftngp13.phx.gbl...
>
> 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

Hi Chris, thanks for the suggestion but I tried what you suggested and it
returns zero records when sorting by columns containing NULL values (still
works perfectly for columns filled with data).

The reason for this is that the SP determines the first value on the page as
NULL (@fld1val). This has the effect that the WHERE clause becomes "WHERE
utbl7.fld5 >= NULL OR (utbl7.fld5 = NULL AND utbl7.Unique_ID > @uniqueid)",
thus returning zero records. I realise there is just a flaw in my logic
somewhere but I just can't seem to get my head round it.

Any further suggestions?

My updated code follows:

*****************************

SP call:

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.fld5 =
@fld1val AND utbl7.Unique_ID > @uniqueid) ORDER BY utbl7.fld5,
utbl7.Unique_ID',5


*************

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 firstpageval = @fld1val, uniqueid = @uniqueid

--SELECT test = @finalselect

EXEC sp_executesql
@finalselect,
N'@fld1val NVARCHAR(64), @uniqueid INT',
@fld1val, @uniqueid



SET ROWCOUNT 0
END

[Back to original message]