Paged Query needs JOIN statement

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Paged Query needs JOIN statement

Author

Topic

rickrehorst
Starting Member

7 Posts

Posted - 2009-10-15 : 15:17:42

I'm Using SQL 2005 from .Net 2008 for a ContactManager program

I have 2 tables 'Company' and 'Person'
For my database connection I use Stored procedures

One of them I use for Reading a fixed amount of records and using parameters to input the StartRow and amount of records that need to be returned.

This is my SP code

ALTER PROCEDURE [dbo].[getContactsPaged]
(
@startRowIndex int,
@maximumRows int,
@Totalrecords int output
)
AS

SELECT PersonID, CompanyID, LastName, FirstName, Email, Telephone, Extra1, extra2 ,Extra3

FROM
(SELECT personID, CompanyID, LastName, FirstName, Email, Telephone, Extra1, extra2 ,Extra3, ROW_NUMBER() OVER (ORDER BY PersonID)
AS RowRank
FROM Person) AS ContactsWithRowNumbers

WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

Select @totalrecords = count(*)
From Person

Return @totalRecords

The thing is that Each Company can have 0 or more persons
Each Person can have 0 or 1 companies

The pkey of the table company = CompanyID
The pkey of the table person = Person ID
The fkey of the table company in the table person = CompanyID (too)

I am sturggling to get a join statement into the existing Stored procedure but keep getting errors.

My best guess is..

ALTER PROCEDURE [dbo].[getContactsPaged]
(
@startRowIndex int,
@maximumRows int,
@Totalrecords int output
)
AS

SELECT PersonID, CompanyID, Company.CompanyName, LastName, FirstName, Email, Telephone, Extra1, extra2 ,Extra3

FROM
(SELECT personID, CompanyID, LastName, FirstName, Email, Telephone, Extra1, extra2 ,Extra3, ROW_NUMBER() OVER (ORDER BY PersonID)
AS RowRank
FROM Person) AS ContactsWithRowNumbers

INNER JOIN Company ON Person.companyID = Company.CompanyID

WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

Select @totalrecords = count(*)
From Person

Return @totalRecords

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-15 : 15:56:18

@totalRecores doesn't need to be both an output parameter AND a RETURN statement. Either one should work. Although I would conditionally set @totalRecords only if it is NULL. That way subsequent page calls wouldn't need to re-query the count(*).


SELECT PersonID
       ,CompanyID
       ,CompanyName
       ,LastName
       ,FirstName
       ,Email
       ,Telephone
       ,Extra1
       ,extra2 
       ,Extra3
from   (
       select p.PersonID
              ,p.CompanyID
              ,c.companyName
              ,p.LastName
              ,p.FirstName
              ,p.Email
              ,p.Telephone
              ,p.Extra1
              ,p.extra2 
              ,p.Extra3
              ,ROW_NUMBER() OVER (ORDER BY PersonID) AS RowRank
       from Person p
       left outer join company c
              on c.companyid = p.companyid
       ) AS ContactsWithRowNumbers

WHERE  RowRank > @startRowIndex 
AND    RowRank <= (@startRowIndex + @maximumRows)

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-15 : 16:26:33

You may want to try (something like) this to see if it is any more efficient. This way you only SELECT all those attributes for the actual rows returned. Your application should pass @totalRecords back in if it already got it from the last call.


;with IDs (personid, rowRank)
as (
       select persionid
              ,row_number() over (order by personid)
       from   person
)
select p.PersonID
       ,p.CompanyID
       ,c.companyName
       ,p.LastName
       ,p.FirstName
       ,p.Email
       ,p.Telephone
       ,p.Extra1
       ,p.extra2 
       ,p.Extra3
from   IDs i
inner join person p
       on p.personid = i.personid
left outer join company c
       on c.companyid = p.companyid
where  i.RowRank > @startRowIndex 
and    i.RowRank <= @startRowIndex + @maximumRows

if @totalRecords is NULL
       Select @totalrecords = count(*) from Person

Be One with the Optimizer
TG

rickrehorst
Starting Member

7 Posts

Posted - 2009-10-17 : 05:31:53

TG Thanx for your reply

I'll test your options and be back on the post

Regards,
Rick

rickrehorst
Starting Member

7 Posts

Posted - 2009-10-17 : 07:36:11

Hi TG

I tested your first option with the left outer join and it works fine.
So the select statement is fine.
Thanx

The Storedprocedure (SP) was designed to display records on a webpage with a self made paging system.
So with the basic select statment and the two input parameters, you are able to take the rows in the select statement and be able to control te startrow and amount of records returned from the SP.
But for my programming I needed the total amount of rows as well in order to calculate the paging system.

I thought that @Totalrecords int output declared the variable and the RETURN makes sure that it gets its value???

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-17 : 16:50:08

>>I thought that @Totalrecords int output declared the variable and the RETURN makes sure that it gets its value???

No, that's not the way it works. Any SP will always "return" a return value. Assuming there are no errors then the return value will be 0. If an error occurs prior to a RETURN statement then the return value will be the error number. You can however explicitly RETURN an integer - any INT you want. That RETURN functionality has no relation to output parameters. If the parameter is declared as OUTPUT then the value of the parameter will be available to a calling process regardless of a RETURN statement.

In my opinion, It's good practice to use non-zero return values only to indicate error information for SPs that will be called directly by users, applications, webservices, etc. I have used return values for "internal use only" SPs to relay other information like state. What I mean is like "helper" SPs that are only called by other SPs.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources