November 29, 2011 at 11:12 am
Dear All,
My Procedure is giving the results of 1000 rows in result set..Now they will sending one parameter for display records per page. and as well as number of pages also. How can I use the row() function here..
November 29, 2011 at 11:16 am
Do you mean Row_Number()?
If so, do an inner query that gets the whole result-set and uses Row_Number() to number the rows, then an outer query that gets only the rows you want. You can do this with a CTE or an inline query in the From clause.
If that's what you mean.
Keep in mind that, unless you set the isolation level to either Repeatable Read or Serializable, and maintain the connection (SPID), you can end up with odd results in that kind of thing because of data changing after a page loads and before the next page is queried.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 29, 2011 at 11:21 am
Thank u...
I need the same, what u said...Thank u Very Much I will implement this...:-)
November 29, 2011 at 11:21 am
a lame example:
CREATE PROCEDURE GETSOMEDATA(@Start INT,
@increment INT)
AS
BEGIN
SET NOCOUNT ON;
WITH MyQuery
AS (SELECT ROW_NUMBER() OVER (ORDER BY name) AS RowNumber,
*
FROM sysobjects)
SELECT *
FROM MyQuery
WHERE RowNumber BETWEEN @Start AND @Start + @increment
END --PROC
GO
EXEC GETSOMEDATA 10,5
EXEC GETSOMEDATA 15,5
Lowell
November 29, 2011 at 11:32 am
In this Proc Can i set a page number...?
November 29, 2011 at 11:34 am
SqlSpider... (11/29/2011)
In this Proc Can i set a page number...?
@start
November 29, 2011 at 11:39 am
Thank U Very Much..
🙂
November 29, 2011 at 11:39 am
page number is the exact same logic, just handled like this:
ALTER PROCEDURE GETSOMEDATA(@PageNumber INT)
AS
BEGIN
SET NOCOUNT ON;
declare @increment int
SET @increment = 10
;WITH MyQuery
AS (SELECT ROW_NUMBER() OVER (ORDER BY name) AS RowNumber,
*
FROM sysobjects)
SELECT *
FROM MyQuery
WHERE RowNumber BETWEEN ((@PageNumber -1) * @increment) AND ((@PageNumber) * @increment)
END --PROC
GO
EXEC GETSOMEDATA 1
EXEC GETSOMEDATA 2
EXEC GETSOMEDATA 3
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply