How to Do It ... (I dont know the exact subject)

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

Transact-SQL (2000)

How to Do It ... (I dont know the exact subject)

Author

Topic

azharrahi
Starting Member

44 Posts

Posted - 2006-08-17 : 08:20:47

This is the orriginal table having info about property.
---------------------------------------------------------------------
PropertyID int
PropertyName varchar(50)
PropertyType varchar(50)
DevelopmentDate datetime
Address varchar(50)
City varchar(50)
Zip varchar(50)
State varchar(50)
Country varchar(50)
Location varchar(50)
Price money
CurrencyName varchar(50)
CurrencySign varchar(50)
Size int
SizeMeasurement varchar(50)
Bedrooms int
Bathrooms int
Direction varchar(50)
Sea bit
Terrace bit
SwimmingPool bit
BedmintonCourt bit
SquashCourt bit
LawnTennisCourt bit
PlayGround bit
Mountain bit
Parking bit
Balcony bit
Furnished bit
ServantQuarters bit
Guard bit
Transport bit
Valid bit

---------------------------------------------------------------------
Now I want to apply The exact searching using Eight fields
--------
City
State
Country
Price
Size
Bedrooms
Bathrooms
---------
The situation is that : If all the fields are selected, then the only matching results found.

Again, the second turn, If one field is missing(or not provided), then the only results must be found which are matched with the remaining fields.

Again If two or more fields are not provided, then the only results must be found which are matched with the remaining fields.

Now I want to define only single stored procedure which can do all these searching based on different number of provided fields. If I try to define each procedure for each criteria or fields, then I have to write a number of stored procedures to fulfill the above requirement.
If there any alternative to this , then kindly tell me as soon as possible.
Thanks

Azhar Rahi
Software Engineer
Eye4tech Pvt Ltd,Lahore
Pakistan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 08:25:30

[code]declare @City varchar(50),
@State varchar(50),
@Country varchar(50),
@Price money,
@Size int,
@Bedrooms int,
@Bathrooms int

select @city = 'london',
@prize = 200

SELECT *
FROM YourTable
WHERE (@City IS NULL OR City = @City)
AND (@State IS NULL OR State = @State)
AND (@Country IS NULL OR Country = @Country)
AND (@Price IS NULL OR Price = @Price)
AND (@Size IS NULL OR Size = @Size)
AND (@Bedrooms IS NULL OR Bedrooms = @Bedrooms)
AND (@City IS NULL OR City = @City)[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 08:25:57

Which 8 fields? You have only provided 7.

Peter Larsson
Helsingborg, Sweden

azharrahi
Starting Member

44 Posts

Posted - 2006-08-17 : 08:36:51

sorry ...it is 7 not 8.

Azhar Rahi
Software Engineer
Eye4tech Pvt Ltd,Lahore
Pakistan

azharrahi
Starting Member

44 Posts

Posted - 2006-08-17 : 08:42:45

now Tell me ...If 'City Is Null', But other 6 Fields Are not null, this query will provide result ... I mean now in this situation, according to my requirement, the result matching the remaining 6 fields must be produced. will it produce the result or not by matching with the 6 fields ?

Azhar Rahi
Software Engineer
Eye4tech Pvt Ltd,Lahore
Pakistan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 08:49:38

1) Any unused search parameter must be NULL for the query above to work
2) Try and run the query against your data and you will see that the query will produce results for the other 6 conditions.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 08:52:27

As always when given a solution or any help here,

TEST, TEST and TEST again. Who knows? You might learn something along the testing...

Peter Larsson
Helsingborg, Sweden

azharrahi
Starting Member

44 Posts

Posted - 2006-08-17 : 08:58:43

Oh Thanks My Dear

..It is really working ...Thanks a lot ....you have solved my tension

Azhar Rahi
Software Engineer
Eye4tech Pvt Ltd,Lahore
Pakistan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources