By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,932 Members | 1,468 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,932 IT Pros & Developers. It's quick & easy.

SQL Server paged recordset problem

P: n/a
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
Sep 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"Steve" <st***@hello.com> wrote in message news:43********@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
Sep 19 '05 #2

P: n/a
Joe
"Chris Hohmann" <no****@thankyou.com> wrote in message
news:Ol**************@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

Sep 19 '05 #3

P: n/a

"Joe" <jo*@hotmail.com> wrote in message
news:e3**************@TK2MSFTNGP09.phx.gbl...
"Chris Hohmann" <no****@thankyou.com> wrote in message
news:Ol**************@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?


WHERE utbl7.fld5 >= @fld1val
OR (utbl7.fld5 = @fld1val AND utbl7.Unique_ID > @uniqueid)
OR (utbl7.fld5 IS NULL AND @fld1val IS NULL AND utbl7.Unique_ID >
@uniqueid)
ORDER BY utbl7.fld5, utbl7.Unique_ID
Sep 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.