|
|
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
Navigation:
[Reply to this message]
|