Building select statements 1

[SueRobbins]

I'd like to know if it's possible to have a stored procedure that uses a local variable to dynamically build a WHERE clause then use that variable in a SELECT statement.

Here's the scenario: Five parameters (@a, @b, @c, @d, @e) are passed to the stored procedure. They represent values to be searched for in columns 1, 2, 3, 4, 5 of a table, respectively. The WHERE clause of the select statement should include a column only if the corresponding parameter has a non-blank value.

So, if there are non-blank values for @a and @c, and the values of the other three parameters are blank, then the select statement should be:
SELECT * from table1 WHERE column1 = @a AND column3 = @c

I'd need a LOT (5 factorial) of different select statements to make this work, so I'm hoping a local variable could be used to build up the WHERE clause, then use it.

Something like:
declare @whereString varchar
set @whereString = ''
if @a <> '' then set @whereString = 'column1 = @a '
if @b <> '' then
begin
if @whereString <> '' then set @whereString += 'and '
set @whereString += 'column2 = @b '
end
if @c <> '' then
begin
if @whereString <> '' then set @whereString += 'and '
set @whereString += 'column3 = @c '
end
Same code for @d and @e.
Now, is there a way to use the @whereString in a select statement?
SELECT * from table1 WHERE @whereString ???

Maybe I'm heading in a wrong direction? Hope someone can help. Thanks.

 

[SQLBill]

Rudy (handle r937) gave me a solution to a similar problem. Check out THREAD183-999189.

-SQLBill

Posting advice: FAQ481-4875

 

[donutman]

where (col1=@a or @a='') and
      (col2=@b or @b='') and
      (col3=@b or @c='') and
      (col4=@b or @d='') and
      (col5=@b or @e='')

Avoid dynamic SQL.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SueRobbins]

Thanks,Karl.

I just tried
where (col1=@a or @a='') and
(col2=@b or @b='') and
(col3=@c or @c='') and
(col4=@d or @d='') and
(col5=@e or @e='')
and it works perfectly!

Now here's a new wrinkle if you're willing to help a bit more:
Col5 is a datetime field, and I need to pass an additional parameter (which would have the value '=' or '>' or '<') to indicate if the expression for col 5 should be "col5=@e" or "col5>@e" or "col5<@e".
Can I accomplish this without resorting to dynamic sql?

Thanks again.

 

[SueRobbins]

Just figured it out:

select * from table1 WHERE
(@a='' OR col1=@a) AND
(@b='' OR col2=@b) AND
(@c='' OR col3=@c) AND
(@d='' OR col4=@d) AND
(@e='' OR
(@f='=' AND col5=@e) OR
(@f='>' AND col5>@e) OR
(@f='<' AND col5<@e))

 

[donutman]

Anytime you figure it out for yourself you deserve a star in my book!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]