sql query

D

dave

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
 
Ê

권민수

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
 
A

Aaron Bertrand [MVP]

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.
 
E

Evertjan.

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.
 
C

Chris Barber

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.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top