conditional query

Question

I have a store procedure with many conditions but one of them depend of the value of one of my parameter. At the moment I'm using if statement and duplicate query, I want to improve but I can't find other way. some idea how

MyStoreProcedure Code

If @paramstate = 'All'
    Select mytable.field1, mytable.field2, mytable.field3, mytable.field4,mytable.field5
    from mytable
    Where mytable.field6 is null
    and mytable.field1 is not null
Else
    Select mytable.field1, mytable.field2, mytable.field3, mytable.field4,mytable.field5
    from mytable
    Where mytable.field6 is null
    and mytable.field1 is not null
    and mytable.state = @paramstate 
End

how can I improve it?

Icarus · Accepted Answer · 2011-10-28 01:26:53Z

1

I think this removes the need for the if/else but beware that this may not be efficient

 Select mytable.field1, mytable.field2, mytable.field3, mytable.field4,mytable.field5
    from mytable
    Where mytable.field6 is null
    and mytable.field1 is not null
    and (mytable.state = @paramstate  or @paramstate = 'All')

edited Oct 28, 2011 at 1:26

answered Oct 28, 2011 at 1:01

Icarus

64k14 gold badges102 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

conditional query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related