How to search multiple fields with stored procedure?

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

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

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

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

Thanks to all...

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

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

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