Add where clause argument if variable is set

Question

I have a WHERE-clause in my SQL-query, and would like to add an argument depending on if the @Fran variable is set.

If @Fran is NULL, then it shouldn't filter the results at all. Otherwise, if @Fran is set, then it should add a WHERE clause "AND franid=@Fran". How could i do that?

Lucifer · Accepted Answer · 2012-05-15 03:14:51Z

3

SELECT ...
FROM ...
WHERE ...
AND ((@Fran IS NULL) OR (@Fran IS NOT NULL AND franid=@Fran));

edited May 15, 2012 at 3:14

Lucifer

29.7k25 gold badges93 silver badges144 bronze badges

answered May 14, 2012 at 16:37

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

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

1 Comment

Anton Gildebrand Over a year ago

Thank you very much! Worked great!

glglgl · Accepted Answer · 2012-05-14 16:52:25Z

0

AND IFNULL(@Fran, franid) = franid

results in TRUE if @Fran is NULL (then it is essentially franid = franid) and in @Fran = franid if it is not NULL.

answered May 14, 2012 at 16:52

glglgl

91.5k13 gold badges157 silver badges230 bronze badges

Collectives™ on Stack Overflow

Add where clause argument if variable is set

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related