Hi This is madhavi

am working with a project with ASP.NET Using VB.NET..

i have requirement that i have to provide the result based on search condition....

First : For Serach i have to search based on given CITY and CATEGORY....



      For this i have written a StoredProcedure like:

************************************************** ************************************************** **************

         Create PROCEDURE YellowPages_Search
(
@city nvarchar(50),
@SearchWord nvarchar(200),
)
AS

declare @sql nvarchar(1000)
set @sql='select * from YellowPages_Userdetails where city='''+@city + '''and (category like ''%' + @SearchWord + '%'' or subcategory like ''%' + @SearchWord + '%'') '

exec(@sql)

GO

************************************************** ************************************************** ************************************************** *******************

Now i want to extend this search condition for LOCATION and SUBCATEGORY

 means my search condition should include CITY , LOCATION , CATEGORY and SUBCATEGORY

(here the location and subcategory may be given or may not be given)
means the search should be based on
  1: city=something and category/subcategory=something
  2: city=something and ctegory=something and location=nothing and category=nothing
  3: city=something and ctegory=something and location=something and category=nothing
  4:city=something and ctegory=something and location=something and category=something
  5:city=something and ctegory=something and location=nothing and category=something


so please help me out

Thanks in Advance,

Madhavi

You can use optional argument
Create PROCEDURE YellowPages_Search
(
@city nvarchar(50),
@SearchWord nvarchar(200),
@is_category bit= null,
@is_subcategory bit= null,
@is_location bit= null,

)
AS

declare @sql nvarchar(1000)

set @sql='select * from YellowPages_Userdetails where city='''+@city + '''
if isnull(@is_category,0)<>0
    set @sql=@sql+ ' and (category like ''%' + @SearchWord + '%'' or subcategory like ''%' + @SearchWord + '%'') '
if isnull(@is_location,0)<>0
    set @sql=@sql+' and (location like ''%' + @SearchWord + '%'') '

and so on.
Here I am confused with columns u have written, also i want to know there is only one searchphrase or you want to accept separate searchword for separate columns

You can directly pass appropriate value, for eg. instead of location flag dircectly pass locationvalue and check if it is not null then append to queryexec(@sql)



urt

Help yourself by helping someone.

Hi madhavi

For this, you need not to build dynamic sql queries. It can be achieved by using normal sql statment
please refer to the below procedure,which tells the SQL statment to take only those param where are passed and neglect others from "where" condition

So here you can pass any combination of the params as needed and pass null to which it is not needed.

exec YellowPages_Search 'bangalore', null, null -- this tells to search only for city
exec YellowPages_Search 'bangalore', null, 'mg road'-- this tells to search only for city and location
exec YellowPages_Search null, 'cyber cafe', 'mg road' -- this tells to search only category and sub category and location
exec YellowPages_Search 'chennai', 'cyber cafe' -- this tells to search only on city and category and sub category

and so on.. you can make query more strong if have more search criteria and columns in that table.

give a try, hope this helps

sp:

create procedure YellowPages_Search
(
    @city nvarchar(50) = null,
    @SearchWord nvarchar(200) = null,
    @location nvarchar(50) = null
)
as
begin
    set @SearchWord = '%' + @SearchWord + '%'
    select
        *
    from
        YellowPages_Userdetails
    where
        (nullif(@city, '') is null or city = @city)
        and (nullif(@SearchWord, '') is null or (category like @SearchWord or subcategory like @SearchWord))
        and (nullif(@location, '') is null or location = @location)
end

With Regards,
Raghavendra Mudugal