Stored Procedure Not Returning Values

Question

Stored Procedure Not Returning Values

Mark-459099

SSC Eights!

Points: 911
More actions
September 28, 2008 at 4:18 pm

#191545

I have the following stored procedure:
SELECT Homes.ListingID, Homes.FullAddress, Contact.Person, Contact.Email, Contact.Phone, Essentials.Beds, Essentials.Baths, Essentials.Style, Essentials.Footage, Essentials.Description, Features.Features, Financials.Price, Financials.Maint, Financials.Taxes, Financials.Down FROM Homes INNER JOIN Contact ON Homes.ListingID=Contact.ListingID INNER JOIN Essentials ON Contact.ListingID=Essentials.ListingID INNER JOIN Features ON Essentials.ListingID=Features.ListingID INNER JOIN Financials ON Financials.ListingID=Features.ListingID WHERE Homes.ListingID=@listid
When I run the procedure in sql server, I get no values returned with none of the listid I have.
However, when I look into the tables, there is data for every listid.
Yet, if I run another stored procedure, they return values flawlessly.
What is it about this stored procedure that no values are being returned even though the data is in the tables? Has anyone experienced this?
Any help will be appreciated.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Could the inner joins be filtering out the rows?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

In other words, start by doing an OUTER JOIN on the homes table with all the other tables. Then, wittle from there.

--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)

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 3

Copy the query in the query window and write the valid listID (against which data exists) replacing @listid and then execute it.

I am sure it will not give the result. If it give the result against the same id you are providing to the Sp as parameter, than there IS a problem...

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Hi Mark

As Gail and Jeff have already suggested, change your joins to LEFT JOINS:

[font="Courier New"]SELECT h.ListingID, h.FullAddress,

c.ListingID, c.Person, c.Email, c.Phone,

e.ListingID, e.Beds, e.Baths, e.Style, e.Footage, e.[Description],

t.ListingID, t.Features,

f.ListingID, f.Price, f.Maint, f.Taxes, f.Down

FROM Homes h

LEFT JOIN Contact c ON c.ListingID = h.ListingID

LEFT JOIN Essentials e ON e.ListingID = h.ListingID

LEFT JOIN Features t ON t.ListingID = h.ListingID

LEFT JOIN Financials f ON f.ListingID = h.ListingID

WHERE h.ListingID = @listid

[/font]

Note that since ListingID values are perpetuated throughout all of the tables, they can all, for the purposes of this query, be considered child tables of the parent table Homes. In your original query, if you had a missing row in the Essentials table then you would also lose any matching rows in the Features and Financials tables. I've taken the liberty of adding ListingID for each table into the output to show which tables have a matching row and which do not. I've also added table aliases to make the query a little more readable.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden