Forum Stats

  • 3,734,274 Users
  • 2,246,935 Discussions
  • 7,857,216 Comments

Discussions

Advanced Search Procedure Help Needed

649044
649044 Member Posts: 3
edited Sep 11, 2008 5:29PM in SQL & PL/SQL
Hello Friends,

I am integrating an advanced search system on my website, where some fields get selected & some are not. There are total 5 fields. I want a query in such a way that when any field gets selected, it should work when 1 or all fields selected. Below is my procedure, it works only when all fields get selected from my interface.

CREATE PROCEDURE SearchCar
(
@manufacturerID numeric,
@modelID numeric,
@typeID numeric,
@Fuel nvarchar(15)=null,
@transmission nvarchar(15)=null
)
AS
--IF DATALENGTH(@manufacturerID) = 0 SET @manufacturerID = 0
--IF DATALENGTH(@modelID) = 0 SET @ModelID = 0
--IF DATALENGTH(@typeID) = 0 SET @typeID = 0
--IF DATALENGTH(@Fuel) = 0 SET @Fuel = NULL
--IF DATALENGTH(@transmission) = 0 SET @transmission = NULL


IF @manufacturerID !=-1 SET @manufacturerID = + @manufacturerID
IF @modelID !=-1 SET @modelID = @modelID
IF @typeID !=-1 SET @typeID = @typeID
IF @Fuel != '-1' SET @Fuel = '%' + @Fuel + '%'
IF @transmission != '-1' SET @transmission = '%' + @transmission + '%'

BEGIN


select * from Carregister where EndDate >= getdate() and (([manufacturerID] =@manufacturerID) and ([modelID] = @modelID) and
([typeID] = @typeID) and ([Fuel] LIKE COALESCE(@Fuel,Fuel)) and ([transmission] LIKE COALESCE(@transmission, transmission)) )


return

END


Regards,

Ujjwal B Soni

Answers

This discussion has been closed.