How to search multiple fields with stored procedure?

Posted by Abhi24h under Sql Server on 10/22/2012 | Points: 10 | Views : 18345 | Status : [Member] | Replies : 6
I'm working on an application for work that is going to query our real estate database. The users want to search based on the City,Bedroom,Posted By,Price Range,Property type,The one thing I want to use stored procedure.Can any one suggest me for this search criteria.

Abhishek Kumar
http://abhishekkumar.in
Redeemer Engisoft Pvt.Ltd



Responses

Posted by: Sourabh07 on: 10/22/2012 [Member] Starter | Points: 25

Up
0
Down
hi...

try to pass these parameters to the procedure........and based on it, perform your searching criteria.

Also, if you don't want to follow the above method then in code file make a temperory table with columns like City,Bedroom,Posted By,Price Range,Property type and assign the values respectively and add this table to the DataSet. Then after generate the Xml of the dataset and pass it as a string to the procedure. Then after extract the fields from the xml in the Procedure and operate your searching criteria.

Sourabh07

Abhi24h, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 10/22/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
abi

try this code


Create Proc Sp_Search
@City varchar(20),
@Bedroom varchar(20),
@PostedBy Datetime,
@PriceRange numeric(18,2),
@Property varchar(20)
as
if(@City<>'')
Select * from <yourTablename> where city like '%Chennai%'
Else
if (@Bedroom<>'')
Select * from <yourTablename> where Bedroom like '%Chennai%'
Else
if (@PostedBy<>'')
Select * from <yourTablename> where PostedBy='%Chennai%'
Else
if (@PriceRange<>'')
Select * from <yourTablename> where PriceRange>=0 and PriceRange<1500
Else
if (@Property<>'')
Select * from <yourTablename> where @Property like '%Chennai%'
End if


Mark as Answer if its helpful to you

Abhi24h, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Seng2hs on: 10/23/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

try this, surely the below query helps you to resolve your problem

Create Proc YourSearchspName
@City varchar(50)=null,
@Bedroom varchar(50)=null
as

Select * from <yourTablename> where citycolumn=isnull(@City,citycolumn) and Bedroomcolumn=isnull(@Bedroom,Bedroomcolumn)


Abhi24h, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rickeybglr on: 10/23/2012 [Member] Starter | Points: 25

Up
0
Down
here is similar type store proc which i have used in my app. just use urs fields:
CREATE PROCEDURE [dbo].[USP_ExtendedSearch]
(
@userName NVARCHAR(30),
@searchUsers NVARCHAR(30),
@Name NVARCHAR(30),
@City NVARCHAR(30),
@email NVARCHAR(30)
)
AS
BEGIN
SELECT a.userID,a.username AS UName,
a.firstname+' '+a.lastname AS Name,
a.useremail AS EmailID,
a.city AS City,
b.imagePath


FROM userRegistrationTB a
JOIN imageTB b
ON
a.userID=b.UserID


WHERE
a.username!=@userName
AND
a.username LIKE '%'+ISNULL(@searchUsers,'')+'%'
AND a.firstname LIKE '%'+ISNULL(@Name,'')+'%'
AND a.city LIKE '%'+ISNULL(@City,'')+'%'
AND a.useremail LIKE '%'+ISNULL(@email,'')+'%'

SET NOCOUNT ON;


END

Abhi24h, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Abhi24h on: 10/29/2012 [Member] Starter | Points: 25

Up
0
Down
Thanks to all...

Abhishek Kumar
http://abhishekkumar.in
Redeemer Engisoft Pvt.Ltd

Abhi24h, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 10/30/2012 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
The correct answer is from rickeybglr

Thank you for posting at Dotnetfunda
[Administrator]

Abhi24h, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response