SQL Server Performance Forum – Threads Archive

selecting data pagewise

Hi, I have a stored procedure, which does a select on a few tables and gets me the output. I have a requirement that the data to be displayed should be pagewise. The first page should display 5 records. The next page should display previous 5 records plus next 5 records. The third page should display output of previous page plus 5 records more. To generalize,
I can pass in the input the page no. and the no of records as an additional parameter in the query. Say, if I pass @pageno as 10 and @noofrecords as 10, I should get totally 100 records. Can someone help me modify the SP. As an example I have a query from the northwind database. CREATE PROCEDURE SP_TOTALORDERS
AS SELECT ord.OrderId,
cu.customerId,
em.employeeId,
from orders ord
inner join customers cu
on ord.customerid=cu.customerid
inner join employees em
on em.employeesID=ord.EmployeeID IF @@ROWCOUNT=0
RETURN 1
RETURN 0
GO EXEC SP_TOTALORDERS Can someone please help with this one. Thanks. Thanks, StarWarsBigBang

Hi All, I was thinking of modifying this proc as follows: ALTER PROCEDURE SP_TOTALORDERS
@pageno INT,
@noofrecordsperpage INT
AS SET @[email protected]*@pageno SELECT TOP @pageno FROM
(
SELECT TOP @noofrecordsperpage FROM
(
SELECT ord.OrderId,
cu.customerId,
em.employeeId,
from orders ord
inner join customers cu
on ord.customerid=cu.customerid
inner join employees em
on em.employeesID=ord.EmployeeID
) AS TR
ORDER BY TR.OrderId ASC)
AS PWR
ORDER BY PWR.OrderId ASC IF @@ROWCOUNT=0
RETURN 1
RETURN 0
GO EXEC SP_TOTALORDERS 10,10 Somehow I am getting syntax errors. Can someone help.
Thanks, StarWarsBigBang

you need to use dynamic string execution.
Also its better to do this pagination at clients side

What is dynamic string execution? Can you modify this procedure and show how I can get the results?
Please respond fast…………….the request is getting heavier by the minute Thanks, StarWarsBigBang

Do a search for "Pagination" and whatever language/environent you are using.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

OK forget what has been done till now. I modified the SP as follows: ALTER PROCEDURE SP_TOTALORDERS
@pageno INT,
@noofrecordsperpage INT
AS DECLARE @sql VARCHAR(8000),@mainsql VARCHAR(8000) SET @[email protected]*@pageno CREATE TABLE #tTotalOrders(
OrderID INT,
CustomerId VARCHAR(5),
EmployeeId INT) INSERT INTO #tTotalOrders
SELECT DISTINCT ord.OrderId,
cu.customerId,
em.employeeId,
from orders ord
inner join customers cu
on ord.customerid=cu.customerid
inner join employees em
on em.employeesID=ord.EmployeeID select @mainsql=’select top’+str(@pageno)+’* from’
select @sql=’select top’+str(@noofrecordsperpage)+’* FROM #tTotalOrders’
exec(@sql)
exec(@mainsql) IF @@ROWCOUNT=0
RETURN 1
RETURN 0
GO EXEC SP_TOTALORDERS 10,10 I still get some syntax errors. I appeal to the expertise of the SQL community to help out on this one.
Thanks much in advance.
Thanks, StarWarsBigBang

Refer these
http://www.aspfaq.com/show.asp?id=2120
http://www.4guysfromrolla.com/webtech/041206-1.shtml
Madhivanan Failing to plan is Planning to fail

Hi,
I have tried this way.. hope this works for you..
Thanks
Parveen. ALTER PROCEDURE SP_TOTALORDERS
@pageno INT,
@noofrecordsperpage INT
AS DECLARE @sql VARCHAR(8000),@mainsql VARCHAR(8000) SET @[email protected]*@pageno CREATE TABLE #tTotalOrders(
OrderID INT,
CustomerId VARCHAR(5),
EmployeeId INT) INSERT INTO #tTotalOrders
SELECT DISTINCT ord.OrderId,
cu.customerId,
em.employeeId
from orders ord
inner join customers cu
on ord.customerid=cu.customerid
inner join employees em
on em.employeeID=ord.EmployeeID
set @sql = ‘select top’+ str(@noofrecordsperpage) +’* FROM #tTotalOrders’ set @mainsql = ‘SELECT TOP’ + str(@pageno) + ‘* from ( ‘ + @sql + ‘)as finalten’ exec (@mainsqL) IF @@ROWCOUNT=0
RETURN 1
RETURN 0
GO Never Underestimate your self. Have a good life

Hi, May i know that last change given by me, was useful to u or if u have done something else May i also know <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /><br /><br />Thanks & Regards.<br /><br />"Never Underestimate your self. Have a good life !!! "

]]>