sql query

hi
Is it possible to write sql query to fetch records from
specific record number?

lets say i have one variable and it stores values such as
1,2,3 or so...

if variable contains, i want to fetch records from 1 to
20.
if it contains 2, then records should be from 21 to 40.

for 3, records would be 41 to 60...

what could be the sql for that?
i dont wannna fetch whole table and then filtering
through code..

any help would be appreciated...
cheers,
dave

sonner
Thu Feb 05 20:39:15 CST 2004

How about this???

CREATE PROCEDURE usp_TempProc
@ContainKey INT=0
AS
BEGIN
SET NOCOUNT ON

DECLARE @FromKey INT
DECLARE @ToKey INT

IF @ContainKey = 0 Or @ContainKey IS NULL
SELECT @ContainKey = 1

SELECT @ToKey = @ContainKey * 20 -- ListCount = 20
SELECT @FromKey = @ToKey - 19

-- Fetch Records

SELECT * FROM TableName WHERE [ContainKey] BETWEEN @FromKey TO @ToKey


SET NOCOUNT OFF
END

Aaron
Thu Feb 05 22:05:33 CST 2004

> SELECT * FROM TableName WHERE [ContainKey] BETWEEN @FromKey TO @ToKey

This assumes that ContainKey represents a consecutive set of values, with no
gaps. Not likely to stay that way in the real world.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Evertjan
Fri Feb 06 05:00:36 CST 2004

Aaron Bertrand [MVP] wrote on 06 feb 2004 in
microsoft.public.inetserver.asp.general:
>> SELECT * FROM TableName WHERE [ContainKey] BETWEEN @FromKey TO
>> @ToKey
>
> This assumes that ContainKey represents a consecutive set of values,
> with no gaps. Not likely to stay that way in the real world.
>

The whole concept of recordnumbers in a relational database is wrong.

Unique numbering, which is something else than consecutive record
numbering, must only be used to point to records inside the WHERE clause
and to identify duplicate records.

Records can have a consecutive numbers in a view,
but those numbers are different in different views and in time.

That is my view.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Chris
Fri Feb 06 06:00:37 CST 2004

I presume that you want to do paging of the recordset:
http://www.aspfaqs.com/webtech/062899-1.shtml

Record numbering is unrealistic and in most circumstances impossible to
maintain.

You are *always* better off constructing a query with appropriate filtering
that returns the exact data that you want or use the functionality above to
ensure paging of that data to reduce the data displayed at any one time.

Chris.

"dave" <anonymous@discussions.microsoft.com> wrote in message
news:aa8801c3ec4e$cd491970$a101280a@phx.gbl...
hi
Is it possible to write sql query to fetch records from
specific record number?

lets say i have one variable and it stores values such as
1,2,3 or so...

if variable contains, i want to fetch records from 1 to
20.
if it contains 2, then records should be from 21 to 40.

for 3, records would be 41 to 60...

what could be the sql for that?
i dont wannna fetch whole table and then filtering
through code..

any help would be appreciated...
cheers,
dave