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.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2002-10-07 : 06:02:01
|
| I am about to tackle something i've been putting off for 6 months because i've found the task quite difficult with my level of SQL experience. What I am looking to do is run a search against the database from a stored procedure with the user choosing from anywhere from 1 to 30 different search criteria. Also I need to be able to page the results. How can I accomplish this?? Can anybody reccomend the best way? Articles? Performance is everything in this situation.Thanks for any helpCheers,Mike123 |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-10-07 : 07:25:12
|
| I would query the database and store the results into a temp table... make sure the temp table has a RowID INT IDENTITY column... next you must decided from which A RowID you want to take to RowID b (a-b results)...say you pass in the page you want to view for the search... @Page = 1 is the start page@StartRowID = (@Page - 1) * @RowsPerPagewhere @RowsPerPage is say 20...@EndRowID = @StartRowID + @RowsPerPagethen you would do a select between for the RowIDSELECT * FROM #results WHERE RowID BETWEEN @StartRowID AND @EndRowIDmake sure that you order the results the same way everytime or your paging will change...for performance make sure your indexes are set properly on the fields that you will be searching ... |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-10-07 : 16:48:15
|
| Also be sure to explicitly declare your ORDER BY or you might not get results the same way each time.By the way, your temp table will go away if you disconnect from the database, so when you go to page 2, you'll probably rebuild and re-populate it each time. Because of this, you might want to look at Graz's articel What's After TOP? |
|
|
|
|
|
|
search SP with paging