May 6, 2003 at 9:48 am
I'm new to SQL Server. Does anyone have/know how I can write a stored proc that will return a certain set of records. For example: myproc 100 150 will return from record 100 to record 150. Thanks for all the help.
May 6, 2003 at 2:53 pm
Is the 100 to 150 a field that you can reference in your table or are you talking simply row numbers? If you have a field just create a stored proc like this:
create procedure spTest
@FromNum int,
@ToNum int
as
select *
from myTable
where myCol between @FromNum and @ToNum
If you want to use row numbers I'll have to get back to you on that one.
Darren
Darren
May 6, 2003 at 2:56 pm
Thanks for the tip. Yes, I would like to use rownum for this stored proc. Once again, thanks for your help. I really appreciate it.
quote:
Is the 100 to 150 a field that you can reference in your table or are you talking simply row numbers? If you have a field just create a stored proc like this:create procedure spTest
@FromNum int,
@ToNum int
as
select *
from myTable
where myCol between @FromNum and @ToNum
If you want to use row numbers I'll have to get back to you on that one.
Darren
May 6, 2003 at 3:32 pm
Can you give me the exact example? The row number is arbitrary depending on how you sort so this seems like an odd thing to do. I have to run but I'll try and check your response tonight.
Darren
Darren
May 6, 2003 at 3:33 pm
I think you cannot do it with one simple SELECT. But if you have rows ordered by a certain column (say, c1), you may need something like this:
INSERT #person
SELECT TOP 100 person_id
FROM person
ORDER BY person_id
SELECT TOP 50 last_name
FROM person
WHERE person_id >
( SELECT TOP 1 person_id FROM #person )
ORDER BY person_id
There might be more elegant way based on the nature of your table's data.
May 6, 2003 at 4:25 pm
depends on the parameters that pass in. for example:
exec myproc 10 , 20
will return from row 10 to row 20
exec myproc 11 , 35
will return from row 11 to row 35
sorting is not required since the stored proc will return the entire record for each row.
quote:
Can you give me the exact example? The row number is arbitrary depending on how you sort so this seems like an odd thing to do. I have to run but I'll try and check your response tonight.Darren
May 7, 2003 at 3:16 pm
Could you give me the purpose of this query in a real example so I can think it through more?
Darren
Darren
May 8, 2003 at 2:25 am
I think he's trying to refer to something like a search engine - so the data is returned in pages of 20 or so instead of 1000 records being sent across the network at once.
I've thought about this sort of process as well, although more in terms of retrieving first 20, then next 20, etc - not retrieving abritary row ranges.
I would do something like...
Assume you have a table called Clients, which has a column ClientID which you are ordering by...
select *
from Clients C
where C.ClientID in (
Select C1.ClientID
from Clients C1 inner join Clients CPrevCount on C1.ClientID >= CPrevCount.ClientID
Group By C1.ClientID
Having Count(*) between @Start and @Finish
)
order by ClientID
But I think that's a bit messy (was off top of my head)
Or perhaps (MUCH MUCH CLEARER - also seems to be simpler execution plan)
select *
from Clients C
where (
Select Count(*) from Clients
Where ClientID < C.ClientID
) between @Start and @Finish
Both took quite a while to run, even with ClientID (in my database) being indexed - although I don't think it is clustered, which would make a difference.
I have seen a better way to do this - look for an article about detecting runs orstreaks in your data on http://www.sqlteam.com (from memory).
Anyone got better ideas!!?? 🙂
Ian
May 8, 2003 at 8:29 am
Been getting really frustrated to have this run in under several minutes to return the rows between 20 & 40 in the database. In the end I have resorted to a temp table solution - which takes about 3 seconds!!
Unfortunately it uses the top clause, which does not accept variables. Two ways around that.... Either use dynamic SQL (which, despite the many discussions about it, I tend to avoid) or create a function which has used the Set RowCount=@NumRows statement, then selecting into a temporary table (ideally one in memory instead of my lazy way below), then issuing Set RowCount=0 to return all rows again. This would let you have some flexibility - just some ideas anyway! I'm sure someone can come up with a better solution 🙂 <g>
select top 40 ClientID
into #TmpClients
from Clients
Order by Surname, Firstname
Select * from Clients
where ClientID in (
Select top 20 ClientID
From #TmpClients
Order by Surname, Firstname Desc
)
Order by Surname, FirstName
Drop #TmpClients
May 11, 2003 at 6:43 pm
My personal opinion would be to return all records that might be used and then process the subsets on the client side. You can easily do the loop by 20 in any client application. Is this really that much data that you want to hit the database only if they choose a Next button or something?
Darren
Darren
May 12, 2003 at 1:58 am
You can do this by exporting all records into temporary table with identity column. But there are two major problems. One is if you have already used an identity column in your table, you cannot add a new identity column to temp table. Second one is if table contains millions of records; there will be some performance problem.
select identity(int, 1,1) as colNum, *
into #temp
from yourTable
select * from #temp where colNum between 5 and 100
Dinesh
mcp mcse mcsd mcdba
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply