Control flow in T-SQL SP using IF..ELSE IF - are there other ways?

Question

I need to branch my T-SQL stored procedure (MS SQL 2008) control flow to a number of directions:

CREATE PROCEDURE [fooBar]
   @inputParam INT
AS
BEGIN
  IF @inputParam = 1
  BEGIN
    ...
  END
  ELSE IF @inputParam = 3
  BEGIN
    ...
  END
  ELSE IF @inputParam = 3
  BEGIN
    ...
  END
END

Is there any other ways? For example, in C# I shoud use switch-case block.

The BEGIN/END isn't necessary unless you are doing multiple things within that portion of the decision logic. — OMG Ponies, Oct 13 '09 at 19:13
@rexem: Yea, I know. I deliberately wrote this because every block contains a lot of code — abatishchev, Oct 13 '09 at 19:29
@rexem, anytime I omitted the begin end, I regretted it later on in maintenance when someone forgot to add them when they added a second step to the branch of the IF. I always use them now. — HLGEM, Oct 13 '09 at 19:56
Just another reason why I find PLSQL to be a better approach - packages and ";" delimited instructions. — OMG Ponies, Oct 13 '09 at 20:01
@rexem: SQL Server _does_ support ; as a delimiter, but it's optional. Except for the `WITH` construct for some reason. But, yeah, as an Oracle developer working on SQL Server, I do miss my PL/SQL packages :-) — Ken Keenan, Oct 13 '09 at 20:26
Stored procs, to perform well, should do just one task, and minimize this type of conditional logic in T-SQL. A better design is to make several procs that each does one task, and decide in your application which to call. This is a fundamental difference from full-on programming languages. Cached plans are a problem with if/then, though SQL 2008 made some progress w/statement-level recompilation. — onupdatecascade, Oct 13 '09 at 23:26

score 49 · Accepted Answer · edited Aug 23 '17 at 12:57

49

IF...ELSE... is pretty much what we've got in T-SQL. There is nothing like structured programming's CASE statement. If you have an extended set of ...ELSE IF...s to deal with, be sure to include BEGIN...END for each block to keep things clear, and always remember, consistent indentation is your friend!

edited Aug 23 '17 at 12:57

Michel de Ruiter

7,131
5
49
74

answered Oct 13 '09 at 18:57

Philip Kelley

39,426
11
57
92

17

I always write my ifs and begin and end before writing the code that will go between the begin and end, saves a whole lot of debugging later to put in the begin ends before any code that goes in between. – HLGEM Oct 13 '09 at 19:00
there exists case statements in T-SQL – shradha Apr 19 '13 at 12:19
7

T-SQL case statements are, how to say it, used as "clauses", parts of other statments--most commonly, SELECT statements. They cannot be used to control programmatic flow as they do in assorted .NET languages. – Philip Kelley May 27 '14 at 13:39
In our copy-paste driven world, it would be nice if this answer contained an actual example ;) – Lukas Eder Jul 07 '17 at 08:31
1

True, but, the original question contains an example of the `if-else if` with `begin/end` structure, and another example would have been redundant. – Philip Kelley Jul 07 '17 at 14:16

score 22 · Answer 2 · edited May 02 '13 at 15:03

22

Also you can try to formulate your answer in the form of a SELECT CASE Statement. You can then later create simple if then's that use your results if needed as you have narrowed down the possibilities.

SELECT @Result =   
CASE @inputParam   
WHEN 1 THEN 1   
WHEN 2 THEN 2   
WHEN 3 THEN 1   
ELSE 4   
END  

IF @Result = 1   
BEGIN  
...  
END  

IF @Result = 2   
BEGIN   
....  
END  

IF @Result = 4   
BEGIN   
//Error handling code   
END

edited May 02 '13 at 15:03

Daniel

10,864
22
84
115

answered May 02 '13 at 14:45

JohnDavid

391
3
4

this would be the exact job for [GOTO](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/goto-transact-sql?view=sql-server-ver15) – adrien Dec 23 '21 at 07:49

score 12 · Answer 3 · edited Jul 06 '10 at 20:08

12

No, but you should be careful when using IF...ELSE...END IF in stored procs. If your code blocks are radically different, you may suffer from poor performance because the procedure plan will need to be re-cached each time. If it's a high-performance system, you may want to compile separate stored procs for each code block, and have your application decide which proc to call at the appropriate time.

edited Jul 06 '10 at 20:08

Tom H

46,766
14
87
128

answered Oct 13 '09 at 19:06

Stuart Ainsworth

12,792
41
46

This is very true. But if the branching can only occur within the procedure (as opposed to your application calling one of several procedures), you're still stuck with a series of IF statements. – Philip Kelley Oct 13 '09 at 19:22
My proc name [execOperation] have been calling from ASP.NET FormView with parameter from dropdown list which contains a list of possible operation types.. So I have no possibility to have a number of separate procs, unfortunately – abatishchev Oct 13 '09 at 19:36

score 4 · Answer 4 · edited Nov 05 '19 at 22:43

4

The Transact-SQL control-of-flow language keywords are:

BEGIN...END
BREAK
CONTINUE
GOTO label
IF...ELSE
RETURN
THROW
TRY...CATCH
WAITFOR
WHILE

edited Nov 05 '19 at 22:43

KyleMit

30,350
66
462
664

answered Oct 13 '09 at 19:14

Cade Roux

88,164
40
182
265

score 1 · Answer 5 · answered Oct 13 '09 at 18:58

1

Nope IF is the way to go, what is the problem you have with using it?

BTW your example won't ever get to the third block of code as it and the second block are exactly alike.

answered Oct 13 '09 at 18:58

HLGEM

94,695
15
113
186

score -1 · Answer 6 · answered Oct 13 '09 at 18:45

-1

CASE expression
      WHEN value1 THEN result1
      WHEN value2 THEN result2
      ...
      WHEN valueN THEN resultN

      [
        ELSE elseResult
      ]
END

https://web.archive.org/web/20210728081626/https://www.4guysfromrolla.com/webtech/102704-1.shtml For more information.

answered Oct 13 '09 at 18:45

Pete Michaud

1,813
4
22
36

2

This is only applicable for queries. I need to write anything inside block, i.e. many lines of code with different queries and other SP's calls – abatishchev Oct 13 '09 at 18:53
14

In T-SQL CASe is an **EXPRESSION** not a controll branch. Very different beasts. – Remus Rusanu Oct 13 '09 at 18:53
This is out of the SELECT statement context. – hoggar Jul 13 '15 at 17:39

Control flow in T-SQL SP using IF..ELSE IF - are there other ways?

6 Answers6

Linked