SQL Server 2008 paginating complex query

Question

I am having a problem paginating the following query. I have tried the example here but it is giving some "cursor" error.

SELECT Countries.CountryID, Countries.Name as CountryName, Customers.FName, Customers.LName, Customers.EMail, LTRIM(Organizations.OrgName) AS OrgName, Organizations.URL, Addresses.City, Addresses.State, Countries.Name,Addresses.Zip 
FROM (((Customers INNER JOIN CustomerMembershipXRef ON Customers.CustomerID = CustomerMembershipXRef.CustomerID) INNER JOIN Organizations ON Customers.OrgID = Organizations.OrgID) INNER JOIN (Countries INNER JOIN Addresses ON Countries.CountryID = Addresses.CountryID) ON Customers.CustomerID = Addresses.EntityID) INNER JOIN Memberships ON (Organizations.OrgID = Memberships.OrgID) AND (CustomerMembershipXRef.MembershipID = Memberships.MembershipID)
WHERE (Memberships.ExpireDate > GETDATE()) AND (Addresses.EntityTypeID=200) AND (Customers.RecordStatus='A') AND (Memberships.RecordStatus='A') AND(Organizations.OrgTypeID=46 OR Organizations.OrgTypeID=55) AND (Addresses.State = 'MI ')
ORDER BY Customers.LName ASC, Organizations.OrgName, Addresses.City, Addresses.State

i need to make it show 50 per page... in mysql it would be simple LIMIT 100,50 for page 3 but MS SQL 2008 does not do it like that... please help me figure the best way to paginate this query.

SELECT CountryID, CountryName, FName, LName, EMail, OrgName, OrgURL, City, State, CountryName,Zip
FROM (SELECT Countries.CountryID as CountryID, Countries.Name as CountryName, Customers.FName as FName, Customers.LName as LName, Customers.EMail as EMail, LTRIM(Organizations.OrgName) AS OrgName, Organizations.URL as OrgURL, Addresses.City as City, Addresses.State as State, Countries.Name as CountryName, Addresses.Zip as Zip, ROW_NUMBER() OVER (ORDER BY Customers.LName ASC, Organizations.OrgName, Addresses.City, Addresses.State) AS RowNum
FROM (((Customers INNER JOIN CustomerMembershipXRef ON Customers.CustomerID = CustomerMembershipXRef.CustomerID) INNER JOIN Organizations ON Customers.OrgID = Organizations.OrgID) INNER JOIN (Countries INNER JOIN Addresses ON Countries.CountryID = Addresses.CountryID) ON Customers.CustomerID = Addresses.EntityID) INNER JOIN Memberships ON (Organizations.OrgID = Memberships.OrgID) AND (CustomerMembershipXRef.MembershipID = Memberships.MembershipID)))
AS PaginatedTable
WHERE (PaginatedTable.RowNum BETWEEN 15 AND 33) AND ((Memberships.ExpireDate > GETDATE()) AND (Addresses.EntityTypeID=200) AND (Customers.RecordStatus='A') AND (Memberships.RecordStatus='A') AND(Organizations.OrgTypeID=46 OR Organizations.OrgTypeID=55) AND (Addresses.State = 'MI '))

error is "Executing SQL directly; no cursor."

What is the exact error? I don't see any cursor in your query. Also I suggest you google "SQL ROW_NUMBER function" — Tab Alleman, Sep 25 '14 at 16:20
yea that is the error "no cursor" but i tried to make it as similar to the example as i could.. im not used to MS SQL and i dont particularly care for it, but this customer uses it so i have to figure this out. — b747fp, Sep 25 '14 at 16:28
Maybe this query is part of a larger script? because it's not possible for what you've posted to generate a "no cursor" error. — Tab Alleman, Sep 25 '14 at 16:40
no it's an actual search query i dont understand why it's giving this error. "Executing SQL directly; no cursor., SQL state 01000 in SQLExecDirect". i updated my post to have the query i am trying to use based on the example i linked — b747fp, Sep 25 '14 at 16:45
That doesn't look like a SQL error... looks like a front-end app error. — Tab Alleman, Sep 25 '14 at 16:51
What are you using on the front end application, PHP? Run the query in SQL Management Studio. If it works there, then it is a ODBC / Application issue! See code below for paging data. — CRAFTY DBA, Sep 25 '14 at 16:52

CRAFTY DBA · Answer 1 · 2014-09-26T18:38:01.670

In SQL 2012 and above, there is the nice OFFSET and FETCH clauses as part of the ORDER BY clause.

http://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

However, since you are using SQL 2008, one solution is to use ROW_NUMBER().

Below, I am paging the records from the product table in adventure works database.

This can be turned into a Stored Procedure and called by the front-end application.

Sincerely

John

www.CraftyDba.com

TSQL:

-- PAGE CNT & ITEM CNT
DECLARE @PAGECNT INT = 5;
DECLARE @ITEMCNT INT = 50;

-- USE COMMON TABLE EXPRESSION
;WITH CTE_PRODUCT AS
(
SELECT 
    ROW_NUMBER() OVER (ORDER BY NAME) as RID,
    *
FROM 
    [AdventureWorks2012].[Production].[Product] P
)
SELECT * 
FROM CTE_PRODUCT
WHERE RID >= @PAGECNT * @ITEMCNT AND RID < (@PAGECNT+1) * @ITEMCNT;

Output:

enter image description here

Hi b747fp,

Did you test your answer??

The reason I used a Common Table Expression (CTE) is that the alias is not available to the WHERE clause due to the logical way the QUERY is processed.

See below, your solution does not work!

John

enter image description here

score 0 · Answer 2 · answered Sep 25 '14 at 17:24

i figured it out there was some issues with placement of parenthasis and column definitions and the orderby... here is working query

SELECT CountryID,ExpireDate,EntityTypeID,MRecordStatus,CRecordStatus,OrgTypeID,CountryName,FName,LName,EMail,OrgName,OrgURL,City,State,CountryName,Zip
FROM (
    SELECT Countries.CountryID as CountryID, Countries.Name as CountryName, Customers.FName as FName, Customers.LName as LName, Customers.EMail as EMail, LTRIM(Organizations.OrgName) AS OrgName, Organizations.URL as OrgURL, Addresses.City as City, Memberships.ExpireDate as ExpireDate, Addresses.EntityTypeID as EntityTypeID, Memberships.RecordStatus as MRecordStatus, Organizations.OrgTypeID as OrgTypeID, Customers.RecordStatus as CRecordStatus, Addresses.State as State, Addresses.Zip as Zip, ROW_NUMBER() OVER (ORDER BY LName ASC) AS RowNum
    FROM((((Customers INNER JOIN CustomerMembershipXRef ON Customers.CustomerID = CustomerMembershipXRef.CustomerID) INNER JOIN Organizations ON Customers.OrgID = Organizations.OrgID) INNER JOIN (Countries INNER JOIN Addresses ON Countries.CountryID = Addresses.CountryID) ON Customers.CustomerID = Addresses.EntityID) INNER JOIN Memberships ON (Organizations.OrgID = Memberships.OrgID) AND (CustomerMembershipXRef.MembershipID = Memberships.MembershipID))
    WHERE ExpireDate > GETDATE() AND EntityTypeID=200 AND Customers.RecordStatus='A' AND Memberships.RecordStatus='A' AND (OrgTypeID=46 OR OrgTypeID=55) AND State = 'MI  '
)
AS PaginatedTable
WHERE PaginatedTable.RowNum BETWEEN 150 AND 200

SQL Server 2008 paginating complex query

2 Answers2