Get a specified number of records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get a specified number of records

Author

Topic

maya13
Starting Member

4 Posts

Posted - 2008-09-22 : 09:25:33

Hi All,

I have some table, lets say PRODUCTS table, with a unique identifier.
I would like to write a store proc which recieves the parameters fromRec and toRec and returns the records from row number fromRec to row number toRec.

Does someone can help me?

Thanks...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 09:28:10

[code]CREATE PROCEDURE GetRecords
@FromRec int
@ToRec int
AS
SELECT
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY uniqueidentifiercolumn) AS Seq,
*
FROM PRODUCTS
)t
WHERE t.Seq BETWEEN @FromRec AND @ToRec
GO[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-22 : 10:15:34

How do you define your range of records?

E 12°55'05.63"
N 56°04'39.26"

maya13
Starting Member

4 Posts

Posted - 2008-09-23 : 02:03:12

Thanks Visakh16

, I'll try it.

Peso, I define the range from my code (c#). My table is very big, so I want each time to get for example 10 records. In the beginning records 1-10, then records 11-20 and so on.

Maya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-23 : 02:26:32

Also search for Pagination

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 02:50:04

quote:
Originally posted by maya13

Thanks Visakh16 , I'll try it.

Peso, I define the range from my code (c#). My table is very big, so I want each time to get for example 10 records. In the beginning records 1-10, then records 11-20 and so on.

Maya

then what you want is this

CREATE PROCEDURE GetRecords
@PageNo int
@PageSize int
AS
SELECT
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY uniqueidentifiercolumn) AS Seq,
*
FROM PRODUCTS
)t
WHERE t.Seq BETWEEN ((@PageNo-1)*@PageSize)+1 AND (@pageNo * @PageSize)
GO

and pass pageno=1,2,... to get corresponding page records
and for above examle put @pageSize=10, you can vary page size by passing other values also.

maya13
Starting Member

4 Posts

Posted - 2008-09-23 : 03:18:03

Thank you very much

!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 03:20:05

quote:
Originally posted by maya13

Thank you very much !!!

welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-23 : 07:18:58

Or this

CREATE PROCEDURE dbo.uspGetRecords
(
	@PageNo INT,
	@PageSize INT
)
AS

SELECT		TOP (@PageSize)
		Number
FROM		(
			SELECT		TOP (@PageNo * @PageSize)
					Number
			FROM		master..spt_values
			WHERE		Type = 'P'
			ORDER BY	Number
		) AS d
ORDER BY	Number DESC

E 12°55'05.63"
N 56°04'39.26"

maya13
Starting Member

4 Posts

Posted - 2008-09-23 : 07:52:48

Thanks !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources