Help with search query please - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Help with search query please

Author

Topic

Ic0n
Starting Member

18 Posts

Posted - 2003-07-30 : 11:43:48

I have a table full of properties(house/flat etc) and i need to create a sproc that searches for properties based on criteria entered in a search form.

The form will pass the following variables to the sproc:

@RentOrBuy bit,
@TypeID int,
@LocationID int,
@MinPrice int,
@MaxPrice int,
@MinBedrooms int,
@MaxBedrooms int

If @TypeID is 0 then i need to search all types, if @LocationID is 0 then i need to search all locations. Price and bedrooms will obviously use the BETWEEN clause.

Database layout image available Here

I have never written a search before so any help would be greatly appreciated.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-07-30 : 12:27:01

You can use CASE if you want only one SELECT.
SELECT * FROM Properties WHERE Price BETWEEN @MinPrice AND @MaxPrice AND Bedrooms BETWEEN @MinBedrooms AND @MaxBedrooms AND TypeID = (CASE WHEN @TypeID = 0 THEN TypeID ELSE @TypeID END) AND LocationID = (CASE WHEN @LocationID = 0 THEN LocationID ELSE @LocationID END)

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources