September 3, 2008 at 1:50 am
Hi all
I am doing a select and putting everything into a recordset, so that then I can do the paging on it. However, in the where, I wish to include a clause, that if the @pagesize = 0 (means return all records), then I will not execute the where clause, but if the @pagesize > 0 then I want to execute.
I came up with something like this which is not working at the moment:-
SELECT * FROM PagesRN
CASE @PageSize
WHEN 0 THEN --do nothing, ie return all records
ELSE -- return the records by the PageNum and the PageSize
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
Can you please tell me what I have wrong
Thanks for your help and time
Johann
September 3, 2008 at 2:09 am
Try this:
Where
Case
When @PageSize = 0 Then 1
When RowNum Between (@PageNum-1) * (@PageSize + 1) And
@PageNum * @PageSize Then 1
Else 0
End = 1
I think it would actually be more readable if you evaluate @PageSize before the query and set the Min and Max rows using variables like this:
If @PageSize = 0
Begin
Set @MinRow = 0
Set @MaxRow = 999999999 -- very large integer you can pick
End
Else
Begin
Set @MinRow = ((@PageNum - 1) * @PageSize) - 1
Set @MaxRow = @PageNum * @PageSize
End
Select
*
FROM
PagesRN
Where
RowNum Between @MinRow and @MaxRow
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 3, 2008 at 2:13 am
First of all, why are you trying to transmit all the records in the table to your client?.
The client must review all the information, or only some pages.
In our organization, we allways limit the number of record to transmit on an aplication on-line.
Anyway, if you need to do it
a) One solution, could be:
SELECT *
FROM PagesRN
WHERE
(@PageSize = 0)
OR
( RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize)
But this solution, avoid the use on any posible index over the RowNum field.
b) Another solution, could be use a stored procedure a set the limits of the select.
IF @PageSize = 0
BEGIN
SET @StartRow = 0
SET @EndRow = 9999999 --the limit of rows in your table
END
ELSE
BEGIN
SET @StartRow = (@PageNum - 1) * @PageSize + 1
SET @EndRow = (@PageNum * @PageSize)
END
SELECT *
FROM PagesRN
WHERE
RowNum BETWEEN (@StartRow AND EndRow)
September 3, 2008 at 2:19 am
Hey I typed faster this time.:D
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 3, 2008 at 2:24 am
Yes.
"next time all try to win the race".
September 3, 2008 at 2:34 am
serinor.e090266 (9/3/2008)
The client must review all the information, or only some pages.
In our organization, we allways limit the number of record to transmit on an aplication on-line.
Hey SSChasing
Thanks for your reply.
I would like to just output the pages the client wants instead of all the pages. Any ideas on how I can do that? Am quite new to SQL server
Thanks
September 3, 2008 at 2:46 am
To transmit only the records needed for a page, we do something like this:
Procedure spProc1
@pParam1 ....
@pParamN,
@pLastRowVisited
AS
-- Here X is the number of rows to present in the client
SELECT TOP X+1
-- Also, limit the transmision to the fields required, don't use * unless it is necesary.
field1, ...., fieldm
FROM PagesRN
WHERE
Conditions of filtering --(based on @pParam1, ... , @pParamN)
--Reposicionamiento (in spanish, i don't know how expressed in english)
AND
RowNumber > @pLastRowVisited
Then you have to control in the client aplication if you have received X registers or X+1. In the last case, you present a button (for example) to indicate the existence of more records to see. And you send a new call to the procedure with the row number of the X'th register showed in the window.
I hope this help you!.
September 3, 2008 at 10:05 am
Hey, sorry for the late reply
Thanks for all your suggestions, will work on that in the near future.
Thanks for your help guys
Johann
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply