How to do this in a single query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2005)

How to do this in a single query?

Author

Topic

Ambikaa
Starting Member

43 Posts

Posted - 2008-08-26 : 01:14:36

Hi,

i have 9 fields in a table name tbl_prty and the fields are keyword, prtygenid,category,state,city,zip,price,features and mortgage.How to do the search of these 9 fields in a single table in a single query?

Table structure
prty_Id bigint
prty_genid varchar(50)
category varchar(50)
StateId varchar(50)
City varchar(50)
features varchar(50)
mortgage varchar(50)

input for the fields
keyword = , prtygenid= PR100,category=single family,state=Tamilnadu,city=chennai,zip=2432,price=0 to 10000,features=alarm and mortgage.=30days

like wise , the input may vary

I may select any one of the fields combination like
keyword, mortgage
only keyword
price......
only state
state,city zip

Previously, i tried with your example but,am not able to get it.

Regards,
Ambika.K

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 01:22:48

[code]select fields
from tbl_prty
where (keyword=@keyword or @keyword is null)
and (prtygenid=@prtygenid or @prtygenid is null)
and (category=@category or @category is null)
and (state=@state or @state is null)
and (zip=@zip or @zip is null)
and (price >= @startprice or @startprice is null)
and (price <= @endprice or @endprice is null)
...[/code]

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-08-26 : 01:23:30

I think this article may help you:

http://weblogs.sqlteam.com/jeffs/archive/2007/09/18/sql-conditional-where-clauses.aspx

Nathan Skerl

Ambikaa
Starting Member

43 Posts

Posted - 2008-08-26 : 01:41:24

quote:

Originally posted by visakh16

select fields
from tbl_prty 
where (keyword=@keyword or @keyword is null)
and (prtygenid=@prtygenid or @prtygenid is null)
and (category=@category or @category is null)
and (state=@state or @state is null)
and (zip=@zip or @zip is null)
and (price >= @startprice or @startprice is null)
and (price <= @endprice or @endprice  is null)
...

Maximum i tried with your solution, but i can't now i clearly explains about the fields

This query i have used it for one of the combination like keyword,prtygenid, state, city, zipcode,features.

here, for keyword input it search into the every fields of tbl_property

for feature i have used fields like
prty_alarm, prty_assistedliving ... if the user selects the any of the feature, 1 is set to the corresponding feature field with that we have to execute the result
SELECT prty_id, prty_genid,StateId,City,prty_Type,prty_SqrFootage,Prty_NoOfBedRooms,
prty_NoOfBathRooms,prty_Price,prty_pcode,prty_status FavStatus FROM tbl_Property
WHERE prty_pcode = 'sdf' AND prty_genid = 'sdf' AND StateId = 'sdf' AND City = 'sdf'
AND prty_Alarm=1 AND (prty_Type LIKE '%sdf%' or prty_id LIKE '%sdf%'or user_Id LIKE '%sdf%' or
prty_genid LIKE '%sdf%' or prty_Address LIKE '%sdf%' or prty_Unit LIKE '%sdf%'or prty_plan LIKE '%sdf%' or
prty_pcode LIKE '%sdf%' or prty_contperson LIKE '%sdf%' or prty_fname LIKE '%sdf%' or prty_lname LIKE '%sdf%'
or prty_pno LIKE '%sdf%' or prty_mobile LIKE '%sdf%' or prty_email LIKE '%sdf%' or prty_Status LIKE '%sdf%'
or prty_Image LIKE '%sdf%' or prty_Type LIKE '%sdf%' or StateId LIKE '%sdf%' or City LIKE '%sdf%' or
Prty_NoOfFloors LIKE '%sdf%' or Prty_NoOfBedRooms LIKE '%sdf%' or prty_NoOfBathRooms LIKE '%sdf%' or
prty_LotSize LIKE '%sdf%' or prty_GargeSize LIKE '%sdf%' or prty_YearBuilt LIKE '%sdf%' or prty_Alarm
LIKE '%sdf%' or prty_AssistedLiving LIKE '%sdf%' or prty_Boating LIKE '%sdf%' or prty_CarpetedFloors
LIKE '%sdf%' or prty_CentralAc LIKE '%sdf%' or prty_ClubHouse LIKE '%sdf%' or prty_ControlledAccess
LIKE '%sdf%' or prty_Courtyard LIKE '%sdf%' or prty_Fireplace LIKE '%sdf%' or prty_FitnessCenter LIKE '%sdf%'
or prty_GasRange LIKE '%sdf%' or prty_GoldCourse LIKE '%sdf%' or prty_MarbleFloors LIKE '%sdf%' or
prty_NatureBikePaths LIKE '%sdf%' or prty_Patio LIKE '%sdf%' or prty_PlayGround LIKE '%sdf%' or
prty_Pool LIKE '%sdf%' or prty_PublicTransport LIKE '%sdf%' or prty_RetirementCommnuty LIKE '%sdf%' or
prty_Spa LIKE '%sdf%' or prty_SportsComplex LIKE '%sdf%' or prty_TennisCourts LIKE '%sdf%' or
prty_WaterFront LIKE '%sdf%' or prty_WheelChair LIKE '%sdf%' or prty_WoodFloors LIKE '%sdf%' or
prty_AdditionalFeatures LIKE '%sdf%' or prty_Price LIKE '%sdf%' or prty_AddHeadline LIKE '%sdf%' or
prty_Description LIKE '%sdf%' or prty_VirtualTourUrl LIKE '%sdf%' or prty_PriceFlexbty LIKE '%sdf%' or
prty_NoOfView LIKE '%sdf%') AND prty_Deleted=0

Ambikaa
Starting Member

43 Posts

Posted - 2008-08-26 : 03:04:52

quote:

Originally posted by visakh16

select fields
from tbl_prty 
where (keyword=@keyword or @keyword is null)
and (prtygenid=@prtygenid or @prtygenid is null)
and (category=@category or @category is null)
and (state=@state or @state is null)
and (zip=@zip or @zip is null)
and (price >= @startprice or @startprice is null)
and (price <= @endprice or @endprice  is null)
...

Hai,

Am i used correct query?

create procedure GetData
@prty_Id bigint = null,
@user_Id bigint = null,
@prty_genid varchar(50) = null,
@CountryId bigint = null,
@StateId varchar = null,
@City varchar(128) = null,
@Prty_NoOfFloors bigint = null,
@prty_Address varchar(128) = null,
@prty_Unit varchar(128) = null,
@prty_plan varchar(128) = null,
@prty_pcode varchar(128) = null,
@prty_contperson varchar(128) = null,
@prty_fname varchar(128) = null,
@prty_lname varchar(128) = null,
@prty_pno varchar(128) = null,
@prty_mobile varchar(128) = null,
@prty_email varchar(128) = null,
@prty_Status varchar(64) = null,
@prty_Image varchar(128) = null,
@prty_Type varchar(128) = null,
@Prty_NoOfBedRooms bigint = null,
@prty_NoOfBathRooms bigint = null,
@prty_SqrFootage varchar(128) = null,
@prty_LotSize varchar(128) = null,
@prty_GargeSize varchar(128) = null,
@prty_YearBuilt varchar(128) = null,
@prty_Alarm bit=null,
@prty_AssistedLiving bit=null,
@prty_Boating bit=null,

@prty_CarpetedFloors bit=null,
@prty_CentralAc bit=null,
@prty_ClubHouse bit=null,
@prty_ControlledAccess bit=null,
@prty_Courtyard bit=null,
@prty_Fireplace bit=null,
@prty_FitnessCenter bit=null,
@prty_GasRange bit=null,
@prty_GoldCourse bit=null,
@prty_MarbleFloors bit=null,
@prty_NatureBikePaths bit=null,
@prty_Patio bit=null,
@prty_PlayGround bit=null,
@prty_Pool bit=null,
@prty_PublicTransport bit=null,
@prty_RetirementCommnuty bit=null,
@prty_Spa bit=null,
@prty_SportsComplex bit=null,
@prty_TennisCourts bit=null,
@prty_WaterFront bit=null,
@prty_WoodFloors bit=null,
@prty_AdditionalFeatures varchar(512) = null,
@prty_Price varchar(128) = null,
@prty_AddHeadline varchar(512) = null,
@prty_Description varchar(518) = null,
@prty_VirtualTourUrl varchar(512) = null,
@prty_PriceFlexbty bigint = null,
@prty_NoOfView bigint = null,
@feature varhcar(512) = null,
@startprice varhcar(512) = null,
@endprice varhcar(512) = null,
@keyword varhcar(512) = null
as
select prty_id, prty_genid,StateId,City,prty_Type,prty_SqrFootage,Prty_NoOfBedRooms,
prty_NoOfBathRooms,prty_Price,prty_pcode,prty_status FavStatus
from tbl_Property
where ((prty_Type LIKE '%@keyword%' or prty_id LIKE '%@keyword%'or user_Id LIKE '%@keyword%' or
prty_genid LIKE '%@keyword%' or prty_Address LIKE '%@keyword%' or prty_Unit LIKE '%@keyword%'or prty_plan LIKE '%@keyword%' or
prty_pcode LIKE '%@keyword%' or prty_contperson LIKE '%@keyword%' or prty_fname LIKE '%@keyword%' or prty_lname LIKE '%@keyword%'
or prty_pno LIKE '%@keyword%' or prty_mobile LIKE '%@keyword%' or prty_email LIKE '%@keyword%' or prty_Status LIKE '%@keyword%'
or prty_Image LIKE '%@keyword%' or prty_Type LIKE '%@keyword%' or StateId LIKE '%@keyword%' or City LIKE '%@keyword%' or
Prty_NoOfFloors LIKE '%@keyword%' or Prty_NoOfBedRooms LIKE '%@keyword%' or prty_NoOfBathRooms LIKE '%@keyword%' or
prty_LotSize LIKE '%@keyword%' or prty_GargeSize LIKE '%@keyword%' or prty_YearBuilt LIKE '%@keyword%' or prty_Alarm
LIKE '%@keyword%' or prty_AssistedLiving LIKE '%@keyword%' or prty_Boating LIKE '%@keyword%' or prty_CarpetedFloors
LIKE '%@keyword%' or prty_CentralAc LIKE '%@keyword%' or prty_ClubHouse LIKE '%@keyword%' or prty_ControlledAccess
LIKE '%@keyword%' or prty_Courtyard LIKE '%@keyword%' or prty_Fireplace LIKE '%@keyword%' or prty_FitnessCenter LIKE '%@keyword%'
or prty_GasRange LIKE '%@keyword%' or prty_GoldCourse LIKE '%@keyword%' or prty_MarbleFloors LIKE '%@keyword%' or
prty_NatureBikePaths LIKE '%@keyword%' or prty_Patio LIKE '%@keyword%' or prty_PlayGround LIKE '%@keyword%' or
prty_Pool LIKE '%@keyword%' or prty_PublicTransport LIKE '%@keyword%' or prty_RetirementCommnuty LIKE '%@keyword%' or
prty_Spa LIKE '%@keyword%' or prty_SportsComplex LIKE '%@keyword%' or prty_TennisCourts LIKE '%@keyword%' or
prty_WaterFront LIKE '%sdf%' or prty_WheelChair LIKE '%@keyword%' or prty_WoodFloors LIKE '%@keyword%' or
prty_AdditionalFeatures LIKE '%@keyword%' or prty_Price LIKE '%@keyword%' or prty_AddHeadline LIKE '%@keyword%' or
prty_Description LIKE '%@keyword%' or prty_VirtualTourUrl LIKE '%@keyword%' or prty_PriceFlexbty LIKE '%@keyword%' or
prty_NoOfView LIKE '%@keyword%') or @keyword is null)
and (prty_genid=@prty_genid or @prty_genid is null)
and (prty_Type=@prty_Type or @prty_Type is null)
and (StateId=@StateId or @StateId is null)
and (City=@City or @City is null)
and (prty_pcode=@prty_pcode or @prty_pcode is null)
and (price >= @startprice or @startprice is null)
and (price <= @endprice or @endprice is null)
and ((@feature) or @feature is null)
and (prty_plan=@prty_plan or @prty_plan is null)

error am getting is

Server: Msg 156, Level 15, State 1, Procedure GetData, Line 93
Incorrect syntax near the keyword 'or'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 03:34:37

use prty_Description LIKE '%' +@keyword+'%' instead of prty_Description LIKE '%@keyword%'

Ambikaa
Starting Member

43 Posts

Posted - 2008-08-26 : 03:44:26

quote:
Originally posted by visakh16

use prty_Description LIKE '%' +@keyword+'%' instead of prty_Description LIKE '%@keyword%'

Hi,
Thanks for ur reply.
how to pass the arguments into the stored procedure ? i need only these fields to be passed

for eg
input like
keyword - sdf
prtygendid- PR100
category - single family
state - chn
city - ds
zip - 6000
startprice - 0
endprice - 100000
mortgage - 30 days
feature - no input

Is that enough to pass only 10 arguments or i have to pass the arguments what i declared in the create procedure getdata.

kindly advice me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 03:46:21

No need to pass explicit values if want them to just use default values provided(null in your case)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources