Multiple WHERE replacement??

Hello,
I have a table relationship that connects a list of Amenities provided by
each Property, such as:
Properties
--
PropertyID int
PropertyName nvarchar(100)
Amenities
--
AmenityID int
AmenityName nvarchar(50)
PropertyAmenities
--
PropertyID int,
AmenityID int
I want to be able to pull a list of Properties that have a certiain
Amenities, such as this static query:
SELECT PropertyID, PropertyName
FROM Properties p
WHERE 1 = 1
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 1)
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 2)
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 3)
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 4)
QUESTION:
What is the best way to setup an efficient Stored Procedure to run this
query, based on a dynamic list of AmenityID's? I'm thinking that there's a
better way, than to dynamically build these WHERE clauses and pass them to
the SQL Server.
I might pass seven integer values on one execution, and three on another.
Thank you for any light you can shed on this.Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Vito D" wrote:

> Hello,
> I have a table relationship that connects a list of Amenities provided by
> each Property, such as:
> Properties
> --
> PropertyID int
> PropertyName nvarchar(100)
>
> Amenities
> --
> AmenityID int
> AmenityName nvarchar(50)
>
> PropertyAmenities
> --
> PropertyID int,
> AmenityID int
>
> I want to be able to pull a list of Properties that have a certiain
> Amenities, such as this static query:
> SELECT PropertyID, PropertyName
> FROM Properties p
> WHERE 1 = 1
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 1)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 2)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 3)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 4)
> QUESTION:
> What is the best way to setup an efficient Stored Procedure to run this
> query, based on a dynamic list of AmenityID's? I'm thinking that there's
a
> better way, than to dynamically build these WHERE clauses and pass them to
> the SQL Server.
> I might pass seven integer values on one execution, and three on another.
> Thank you for any light you can shed on this.
>
>|||Since you didnt post DDL, I didn't build the tables and test this, but give
it a shot.
This is basically relational division, which you can find better examples of
if you search for posts by CELKO in this news group, or just do a google
search. I have nto really worked with this much, but I think this should
work.
Combine this with dynamic SQL to build your in clause (or populate a temp
table with the values for your in clause).
This code assumes that you have PKs on properties(PropertyID),
PropertyAmenities (PropertyID, AmenityID) and Amenities(AmenityID).
SELECT prop.PropertyID, prop.PropertyName, count(1)
FROM Properties prop
inner join PropertyAmenities propamen
on propamen.PropertyID = prop.PropertyID
where propamen.AmenityID in (1,2,3,4)
having count(1) = (select count(1)
from Amenities amen
where amen.AmenityID in (1,2,3,4)
)
"Vito D" <VdeleteDeCarlo@.ThePulse.remove.com> wrote in message
news:uVEt8btbGHA.536@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I have a table relationship that connects a list of Amenities provided by
> each Property, such as:
> Properties
> --
> PropertyID int
> PropertyName nvarchar(100)
>
> Amenities
> --
> AmenityID int
> AmenityName nvarchar(50)
>
> PropertyAmenities
> --
> PropertyID int,
> AmenityID int
>
> I want to be able to pull a list of Properties that have a certiain
> Amenities, such as this static query:
> SELECT PropertyID, PropertyName
> FROM Properties p
> WHERE 1 = 1
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 1)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 2)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 3)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 4)
> QUESTION:
> What is the best way to setup an efficient Stored Procedure to run this
> query, based on a dynamic list of AmenityID's? I'm thinking that there's
a
> better way, than to dynamically build these WHERE clauses and pass them to
> the SQL Server.
> I might pass seven integer values on one execution, and three on another.
> Thank you for any light you can shed on this.
>

Multiple tables from within a stored procedure - how?

Hi,
I'm currently analyzing SSRS as a potential replacement for Crystal Reports
(and yes, it's definitely going to replace it). I do have one issue,
however... I'm attempting to point SSRS to a stored procedure that returns
four separate tables, but only the first one seems to be returned.
Is there a way to have access to each of the tables within the sproc?
Regards,
ScottNo there is not and this has not changed in RS 2008.
The most you can do is to have a parameter that selects which one you want
and then when calling the stored procedure set that parameter to a constant
value. If used in a report and all four are desired you have to call it four
times.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott McNair" <smcnair@.beachexpress.takethispartout.com> wrote in message
news:Xns9A218ECAC3E7Adonquixote235gmailco@.207.46.248.16...
> Hi,
> I'm currently analyzing SSRS as a potential replacement for Crystal
> Reports
> (and yes, it's definitely going to replace it). I do have one issue,
> however... I'm attempting to point SSRS to a stored procedure that returns
> four separate tables, but only the first one seems to be returned.
> Is there a way to have access to each of the tables within the sproc?
> Regards,
> Scott