Converting Navigation to SP
-
I have a table of 2000+ records that I need to divide up and call dynamically. I'm converting everything over to stored procedures and can no longer use "top" and cannot use "rowcount" in my subselect. myNumPost represents the number of records per page and I multiply it by request("nav") which is my page number.
Here is the query that needs to be converted:
select top 20(objID) from tblObject where objID not in (select top "& myNumPost*request("nav") &"(objID) from tblObject order by objID asc) order by objid asc
-
I can't set top count using a parameter.MS SQL does not allow it apparently. And the rowcount is set before I begin my select statement so I can't seem to use it for my subselect. Maybe there's a way to do it that I just don't see. If you know of a way, I beg of you, share your knowledge!
-
You might have to write an EXEC statement, but it looks like you're trying to do this from ASP. I'm not completely familiar with doing this sort of thing in ASP, but if you wanted to write a stored proc, this is how you'd do it (assuming @number is the parameter passed in)...
EXEC('select top ' + @number + '(objID) from tblObject where objID not in (select top ' + @number + '(objID) from tblObject order by objID asc) order by objid asc');
-
Here is the code from the ASP page
Set objNav = Server.CreateObject("ADODB.Command")
ObjNav.ActiveConnection = cn
objNav.CommandText = "sp_navigation"
objNav.CommandType = 4
SET objParam2 = objNav.CreateParameter("@myNumPost",131, 1, 4)
objNav.Parameters.Append objParam2
objNav.Parameters("@myNumPost") = myNumPost
Set rstData = objNav.Execute
Here is the stored procedure (with the bad syntax) :
CREATE PROCEDURE dbo.sp_navigation
@myNumPost int
as
select top 20(objID), * from tblObject where objID not in
(select top @myNumPost(*) from tblObject order by objID asc)
order by objid asc
GO
-
you can do this using a temp table also, look here for info.
http://www.developersdex.com/gurus/code/836.asp
-
Yep... temp tables and all that other stuff work fine on tiny tables containing 2000 or even 10000 rows... try it with a million rows...
This, of course, can be done more easily in SQL Server 2005 because of the programmable TOP clause, but this is an example of what I used on a 1.2 million row customer table at work... the first page of 100 is usually returned in about 16 milli-seconds... the 12,000th page is returned in only 465 milli-seconds. Both page size and page number are parameters.
Of course, you'll need to modify the code to meet your specific needs.
declare @start datetime
set @start = getdate()
--===== Declare the local variables
DECLARE @PageSize INT --How many rows to appear per page
DECLARE @PageNum INT --What page number to appear
DECLARE @Skip INT --Working variable for where to start for page
DECLARE @sql VARCHAR(8000) --Holds dynamic SQL
--===== Set the local variables for pagesize and page
-- PageSize and PageNum would be parameters in a stored proc
SET @PageSize = 100
SET @PageNum = 4000
SET @Skip = @PageSize*@PageNum
--===== Create the dynamic SQL statement that will produce the page
SET @sql =
'
SELECT c.*
FROM dbo.Customer c,
(
SELECT TOP '+STR(@PageSize)+ ' *
FROM dbo.Customer WITH (NOLOCK)
WHERE CustID NOT IN (SELECT TOP '+STR(@Skip)+' CustID
FROM dbo.Customer
ORDER BY CustID)
ORDER BY CustID
) d
WHERE c.CustID = d.CustID
'
--===== Produce the page
EXEC (@SQL)
print CONVERT(varchar(30),getdate()-@start,114)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you all for your help! My pages are up and running
-
Can you post what you used? We can all learn a new trick or two....
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply