CREATE PROCEDURE [dbo].[sp_fyndetsearchparams]
(@pricefr int, @priceto int, @milagefr int, @milageto int, @yearfr int, @yearto int, @model int, @make int, @cylinderfr int, @cylinderto int, @county int)
AS
SELECT mc.mcID, mc.mcname, mc.mcheadline, mc.mcprice, mc.mcmilage, mc.mcyear, mc.mcmodel, mc.mcmake, mc.mccylinder, mc.mccounty, mc.mcpicurl, mc.mcverified, mc.mcdate, county.countyID, county.countychar
FROM mc, county
WHERE mc.mccounty = county.countyID AND
mc.mcprice BETWEEN @pricefr AND @priceto AND
mc.mcmilage BETWEEN @milagefr AND @milageto AND
mc.mcyear BETWEEN @yearfr AND @yearto AND
mc.mcmodel @model AND <----- problem
mc.mcmake @make AND <----- problem
mc.mccylinder BETWEEN @cylinderfr AND @cylinderto AND
mc.mccounty @county <----- problem
ORDER BY mc.mcID DESC
GO

I've tried to get this search thing working but it doesn't want to swallow this query

the problem is that
if the user doesn't choose from the drop downlist i get a 0 and then in the parameter i have this if code

<%
Dim RsView__varMake
RsView__varMake = "<> -1"
if (Request.QueryString("make" > "0" then RsView__varMake = "= "&Request.QueryString("make"
%>

thats the problem, I want the query to show all the records "<> -1" if nothing is selected and if something is selected it should show "= " it works if i dont use the stored procedures, bacause I can have the sql query look this way

mc.mcmake " + Replace(RsView__varMake, "'", "''"

Is there another way of solving this and if so how??

Cheers

// Leon

well, if you are going to be stringing the sql statement together like that, then you can't use a traditional SP.

Two possiblities...
1. create the SQL string in ASP, and execute it, but then you lose the encapsulation of an SP.

2. use T-SQL to string the SQL statement in the SP, and use the execute command. a bit more difficult for the average coder (don't know your status in this), but in my opinion a better solution.

(something like this

declare @sql varchar(255)
set @sql = 'SELECT * FROM table WHERE ' + @paramValue

)

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/

I think your problem is that you have left out the <b>=</b> signs:

WHERE mc.mccounty = county.countyID AND
mc.mcprice BETWEEN @pricefr AND @priceto AND
mc.mcmilage BETWEEN @milagefr AND @milageto AND
mc.mcyear BETWEEN @yearfr AND @yearto AND
mc.mcmodel <b>=</b> @model AND &lt;----- problem
mc.mcmake <b>=</b> @make AND &lt;----- problem
mc.mccylinder BETWEEN @cylinderfr AND @cylinderto AND
mc.mccounty <b>=</b> @county &lt;----- problem
ORDER BY mc.mcID DESC

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo