Multiple Search Words for SQL

[Keith]

I have a small form which searches a SQL DB on several fields - ie.

WHERE Field1 = xxx AND Field2 = xxx AND Field3 = xxx

How can I do this so that if one search criteria is left blank, it ignores
it instead of trying to match it in the DB?

 

[Aaron [SQL Server MVP]]

You could say:

WHERE (@param1 IS NULL OR column1 = @param1)
or
WHERE column1 = COALESCE(@param1, column1)

Neither of these will use indexes, so performance might not be optimal.

If you have a small number of options, you could use IF statements to choose
a static SELECT statement, e.g.

IF @param1 IS NOT NULL AND @param2 IS NOT NULL
SELECT * FROM tbl WHERE column1 = @param1 AND column2 = @param2

IF @param1 IS NULL AND @param2 IS NOT NULL
SELECT * FROM tbl WHERE column2 = @param2

etc etc

If the number of options is large, you could try dynamic SQL (either a
stored procedure that builds the statement and executes it, or having ASP
build the query and send it as adCmdText).

 

[Bullschmidt]

And here's a related article I put together that hopefully might give
you some more ideas too:

ASP Design Tips - Search For an Expression on Multiple Fields
http://www.bullschmidt.com/devtip-searchmultiplefields.asp

Best regards,
J. Paul Schmidt, Freelance ASP Web Consultant
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!