sql server programming: multilpe where clause -- sql server programming

Re: multilpe where clause

J. M. De Moor
6/30/2007 3:49:09 PM

sql server programming:

rodchar

If you know which columns, just allow optional input parameters (passed
as NULL):

CREATE PROCEDURE dbo.MyProc @fname AS VARCHAR(15), @lname AS VARCHAR(25)
AS

SELECT * FROM Employees
WHERE last_name = COALESCE(@lname, last_name)
AND first_name = COALESCE(@fname, first_name)

The performance purists will probably chime in that it is better to
explicitly trap for the NULL parameters:

WHERE (@lname IS NOT NULL OR last_name = @lname)
...etc.

This has the advantage of handling combinations of multiple columns in
the criteria.

Joe

[quoted text, click to view]

multilpe where clause

rodchar
6/30/2007 6:20:02 PM

hey all,
i'm trying to create a stored proc that has the following flexibility:

select * from employees
where firstname = "%searchclause%"

select * from employees
where lastname = "%searchclause%"

my first guess is to have a parameter indicating which field the user wants
to search and then have an if statement separating the 2 sql statements.
would this be the best way?

thanks,

Re: multilpe where clause

rodchar
6/30/2007 7:26:02 PM

thank you very much for the help.

[quoted text, click to view]

Re: multilpe where clause

Tibor Karaszi
7/1/2007 12:00:00 AM

You might want to read this: http://www.sommarskog.se/dyn-search.html

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.