stored proc queries

[Crisps]

Hi, given a simple stored procedure prototype as follows

CREATE PROCEDURE dbo.lookup 
	@first_name	char (30),
	@last_name	char (30),
AS
	/*work*/
GO

How would I go about adding a select statement(s) for all possible combinations of input parameters?

i.e. I would like to do the following

if @first_name not empty and last_name not empty then
    SELECT * FROM names WHERE first = @first_name AND last = @last_name
elsif @first_name not empty
    SELECT * FROM names WHERE first = @first_name 
elsif @last_name not empty
    SELECT * FROM names WHERE last = @last_name 
end

Now this is just an example, including only two input fields, my real SP has several so there are hundreds of cobinations so the if else if style would be of no use.

Is there perhaps a way to build the query as a string or something (I'm a C++ programmer and don't write SP's that much so stating the obvious if fine )

Thanks,

 

[gmmastros]

You can build the query as a string and then run it. This is called [google]Dynamic SQL[/google] and is generally a bad idea when writing queries. However, if you are willing to get a little fancy with your where clause, you can get the same results without using dynamic sql.

SELECT * 
FROM   names 
WHERE  (@first_name = '' or first = @first_name)
       AND (@last_name = '' or last = @last_name)

So, if you pass in an empty string for first name, the where clause will look like this...

Where ('' = '' or first = @first_name)

Since '' = '', the OR'd part has no effect. If you pass in a value, then your where clause would look like this...

Where ('SomeValue' = '' or first = 'SomeValue')

The first part will evaluate to False, but the second part will evaluate to true for those rows where first actually equals 'somevalue'.

Make sense?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Crisps]

makes perfect sense, something like that was exactly what I was looking for, Thanks.

 

[gmmastros]

I forgot to mention one of my RULES. Whenever you use an OR in a where clause, you should ALWAYS use parenthesis.

Take a close look at the way I used them in my previous post. the parenthesis are where they belong. However, if you remove the parenthesis, you WILL get the wrong results. I mention this only because I see lot's of questions that can be resolved by putting parenthesis where they belong.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom