Benefits of using a Case statement over an If statement in a stored procedure?

Question

Hi are there any pros / cons relating to the speed that a stored procedure executes when using an IF statement or choosing to use a CASE statement instead?

Answer 1

I try not to use IFs if I can avoid it, because they are non transactional, i.e. you have generally no guarantee that the condition of the IF will still be valid inside the BEGIN... END block of the IF.

I assume that you mean using CASE statements inside a SQL SELECT, for example. If the usage is meant to avoid an IF then, by all means, do it.

The golden rule of SQL is: let the server figure out HOW to do things, tell the server what you WANT.

Answer 2

Don't concern yourself with any speed difference between a case and an IF statement. Because you're dealing with a database, the amount of time it takes to write or read from disk is going to dwarf the time it takes the CPU to branch via an IF or Case. You should focus instead on which makes your code more readable and maintainable.