SQL Server Performance Forum – Threads Archive

A query that returns very few records

Hi,
I have a SP that makes a select in Offers table according to some parameters selected by user in the application: USE market3
GO
ALTER PROCEDURE find_offers @Quality_num tinyint, @Caliber_min numeric(6,2), @Caliber_max numeric(6,2), @Image tinyint, @Rippening_num tinyint
As Select Offer_id, User_num, Offer_date, Offer_title, Company_name, City_name
From Offers As offe WHERE EXISTS (SELECT * FROM Offers_quality As of_qual WHERE offe.Offer_id = of_qual.Offer_num And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0))
And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE offe.Offer_id = of_qual.Offer_num And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And ((of_qual.Caliber BETWEEN @Caliber_min And @Caliber_max And @Caliber_min) Or (@Caliber_min = 0 And @Caliber_max = 0))
And EXISTS (SELECT * FROM Offers_rippening As of_rip WHERE (of_rip.Offer_qual_num In(SELECT Offer_qual_id FROM Offers_quality As of_qual WHERE offe.Offer_id = of_qual.Offer_num And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0)) And of_rip.Rippening_num = @Rippening_num) Or @Rippening_num = 0)
And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE offe.Offer_id = of_qual.Offer_num And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And ((of_qual.Image_name Is Not Null And @Image = 1) Or @Image = 0)) GO
Somebody knows why I am getting so few records from Offers table? When user doesn#%92 t specify any parameter or only specify some of them, I want all the records from Offers table are returned. How can I achieve it? Is it possible is a question of null values that when are found through â€˜Where#%92 clause the record isn#%92 t returned? Thank you

Your bypass option with the 0 value should bypass the EXISTS clause as a whole: it is now blocking records because there are no records in the other tables where the value is 0. So your WHERE statement needs to be WHERE (@param1 = 0 OR EXISTS (SELECT * FROM othertable1 WHERE column = @param1))
AND (@param2 = 0 OR EXISTS (SELECT * FROM othertable2 WHERE column = @param2)) etc.

Hi Adriaan, [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I tried this: <pre id="code"> WHERE (@Quality_num = 0 Or EXISTS (SELECT * FROM Offers_quality As of_qual WHERE offe.Offer_id = of_qual.Offer_num And (of_qual.Quality_num = @Quality_num))) And ((@Quality_num = 0 And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE offe.Offer_id = of_qual.Offer_num And ((of_qual.Caliber BETWEEN @Caliber_min And @Caliber_max And @Caliber_min) Or (@Caliber_min = 0 And @Caliber_max = 0))) Or EXISTS (SELECT * FROM Offers_quality As of_qual WHERE offe.Offer_id = of_qual.Offer_num And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And ((of_qual.Caliber BETWEEN @Caliber_min And @Caliber_max And @Caliber_min) Or (@Caliber_min = 0 And @Caliber_max = 0)))) And ((@Quality_num = 0 And EXISTS (SELECT * FROM Offers_rippening As of_rip WHERE (of_rip.Offer_qual_num In(SELECT Offer_qual_id FROM Offers_quality As of_qual WHERE offe.Offer_id = of_qual.Offer_num) And of_rip.Rippening_num = @Rippening_num) Or @Rippening_num = 0)) Or EXISTS (SELECT * FROM Offers_rippening As of_rip WHERE (of_rip.Offer_qual_num In(SELECT Offer_qual_id FROM Offers_quality As of_qual WHERE offe.Offer_id = of_qual.Offer_num And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0)) And of_rip.Rippening_num = @Rippening_num) Or @Rippening_num = 0))) And (@Image = 0 Or EXISTS (SELECT * FROM Offers_quality As of_qual WHERE offe.Offer_id = of_qual.Offer_num And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And (of_qual.Image_name Is Not Null And @Image = 1))) </pre id="code"> But it does the same

This can get complicated … One thing I notice in the second EXISTS clause is that you’re not bypassing the @Caliber_min and @Caliber_max parameters. The second clause actually has @Quality_num and @Caliber_min and @Caliber_max, so you need to cover quite a lot of variations: WHERE
— 1st clause
(@param1 = 0 OR EXISTS (SELECT * FROM othertable1 WHERE column = @param1))
— 2nd clause
AND (
((@param1 = 0 AND @Param_Min <> 0 AND @Param_Max <> 0) AND EXISTS (SELECT * FROM othertable2 WHERE column2 BETWEEN @Param_Min AND @Param_Max))
OR ((@param1 <> 0 AND @Param_Min <> 0 AND @Param_Max <> 0) AND EXISTS (SELECT * FROM othertable2 WHERE column = @param1 AND column2 BETWEEN @Param_Min AND @Param_Max))
OR ((@param1 <> 0 AND @Param_Min = 0 AND @Param_Max = 0) AND EXISTS (SELECT * FROM othertable2 WHERE column = @param1))
)
— 3rd clause ……………… etc., etc. You might be better off building the whole query as dynamic SQL …

Adriaan, this the code I written: (And works fine! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] It seems to work fast. What do you think?) <pre id="code"> WHERE (@Quality_num = 0 Or EXISTS (SELECT * FROM Offers_quality As of_qual WHERE offe.Offer_id= of_qual.Offer_num And (of_qual.Quality_num = @Quality_num))) And (((@Quality_num = 0 And @Caliber_min <> 0 And @Caliber_max <> 0) And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE (offe.Offer_id= of_qual.Offer_num) And (of_qual.Caliber BETWEEN @Caliber_min And @Caliber_max))) Or ((@Quality_num = 0 And @Caliber_min <> 0 And @Caliber_max = 0) And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE (offe.Offer_id= of_qual.Offer_num) And (of_qual.Caliber >= @Caliber_min))) Or ((@Quality_num = 0 And @Caliber_min = 0 And @Caliber_max <> 0) And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE (offe.Offer_id= of_qual.Offer_num) And (of_qual.Caliber <= @Caliber_max))) Or (@Quality_num = 0 And @Caliber_min = 0 And @Caliber_max = 0) Or (@Quality_num <> 0 And @Caliber_min = 0 And @Caliber_max = 0) Or ((@Quality_num <> 0 And @Caliber_min <> 0 And @Caliber_max <> 0) And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE (offe.Offer_id= of_qual.Offer_num And of_qual.Quality_num = @Quality_num) And (of_qual.Caliber BETWEEN @Caliber_min And @Caliber_max))) Or ((@Quality_num <> 0 And @Caliber_min <> 0 And @Caliber_max = 0) And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE (offe.Offer_id= of_qual.Offer_num And of_qual.Quality_num = @Quality_num) And (of_qual.Caliber >= @Caliber_min))) Or ((@Quality_num <> 0 And @Caliber_min = 0 And @Caliber_max <> 0) And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE (offe.Offer_id= of_qual.Offer_num And of_qual.Quality_num = @Quality_num) And (of_qual.Caliber <= @Caliber_max)))) And ((@Quality_num = 0 And @Rippening_num = 0) Or ((@Quality_num = 0 And @Rippening_num <> 0) And EXISTS (SELECT * FROM Offers_Rippening As of_rip WHERE (of_rip.Offer_qual_num In(SELECT Offer_qual_id FROM Offers_quality As of_qual WHERE offe.Offer_id= of_qual.Offer_num) And of_rip.Rippening_num = @Rippening_num))) Or (@Quality_num = 0 And @Rippening_num = 0) Or (@Quality_num <> 0 And @Rippening_num = 0) Or ((@Quality_num <> 0 And @Rippening_num <> 0) And EXISTS (SELECT * FROM Offers_Rippening As of_rip WHERE (of_rip.Offer_qual_num In(SELECT Offer_qual_id FROM Offers_quality As of_qual WHERE offe.Offer_id= of_qual.Offer_num And of_qual.Quality_num = @Quality_num) And of_rip.Rippening_num = @Rippening_num)))) And (@Image = 0 Or EXISTS (SELECT * FROM Offers_quality As of_qual WHERE offe.Offer_id= of_qual.Offer_num And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And ((of_qual.Image_name Is Not Null And @Image = 1)))) </pre id="code">

I’m having problems with two pairs of lines:
(1)
Or (@Quality_num = 0 And @Caliber_min = 0 And @Caliber_max = 0)
Or (@Quality_num <> 0 And @Caliber_min = 0 And @Caliber_max = 0)
(2)
Or (@Quality_num = 0 And @Rippening_num = 0)
Or (@Quality_num <> 0 And @Rippening_num = 0) Correct me if I’m wrong, but I have a hunch that pretty much all rows pass any combination of criteria when you include these two as free-floating "OR" lines.

I don#%92 t understand very well what you mean, but I tried to erase these two pairs of lines you said and none record was returned in any case. I need to put these two free-floating "OR" lines so that the SP can work properly. I suppose it is thus because, as you said, it is needed to cover all possible combination of expected parameter values, because if some combination is not covered in the SP, and it receives that combination then none record is returned in any case. I don#%92 t know why, since simply should to bypass the condition â€˜And#%92 that doesn#%92 t find any combination, and continue returning the rest of table records..

Do you think it would be better building the query as dynamic SQL? If so, Why? And, what is the difference between this (SELECT * FROM Offers_quality As of_qual WHERE..) and (SELECT 1 FROM Offers_quality As of_qual WHERE..) ? Thanks

The idea behind dynamic SQL is that your select statements no longer have any bypass criteria,
and you only the ones that actually do something. That could help clear up two things: (1) why no records are returned – you may find that the remaining criteria just rule out all rows, period … (2) exactly how the correlated subqueries should be AND/OR-ed.

]]>