Stored proc to search multiple words and dropdowns

[partymong]

Hi All,
I want to create a stored procedure to deal with a multiple word search (via an input box) but allow the search to be narrowed down via 3 drop down lists.

I want to search for the inputted words, but where the values in drop down lists are met...

I am sure I could be approaching this in a simplier way, and any help would be greatly appreciated.

Thankyou in Advance for yor help!

Below is my attempt:- (which sometimes returns no records even though conditions are met)

The @Array is the multiple words parameter
The @Separator is the delimiter for the multiple search words e.g a space
The @version = is a value within the DatasetVersion field
(the value is generated from dropdown list number 1)
The @flavour = is a value within the IsNew field
(the value is generated from another dropdown list number 2)
The @blah = is a value within the lastModifiedDate field
(the value is generated from the dropdown list number 3)


===========================================================
Create PROCEDURE sp_search2
(@Array varchar(255),
@separator char(1),
@version varchar (50),
@flavour varchar (50),
@blah varchar (50))



AS
BEGIN TRAN

SET NOCOUNT ON
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000)
set @array = @array + @separator
create table #result
(
datasetID int not null,
datasetSource text null,
datasetName varchar (255) null,
datasetDesc text null,
datasetPathName varchar (255) null,
clicks varchar (50) null,
datasetVotes int null,
datasetRating int null,
datasetVersion varchar (255) null,
lastModifiedDate varchar (50) null,
firstCreatedDate varchar (50) null,
isNew varchar (50) null,
isUpdated varchar (50) null,
datasetLanguage varchar (20) null,
upload_user varchar (50) null,
linkedno int null
)
-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert into #result
SELECT *
FROM datasets
WHERE isNew = @flavour and lastModifiedDate = @blah and datasetVersion = @version
and (datasetDesc LIKE '%' + @array_value + '%' or datasetSource LIKE '%' + @array_value + '%' or clicks LIKE '%' + @array_value + '%')

select @array = stuff(@array, 1, @separator_position, '')
end

select * from #result order by firstCreatedDate desc
drop table #result

SET NOCOUNT OFF
COMMIT TRAN