views:

1406

answers:

5

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.

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

      [
        ELSE elseResult
      ]
END

http://www.4guysfromrolla.com/webtech/102704-1.shtml For more information.

Pete Michaud
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
In T-SQL CASe is an **EXPRESSION** not a controll branch. Very different beasts.
Remus Rusanu
+3  A: 

IF...ELSE...ENDIF 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, consistant indentation is your friend!

Philip Kelley
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
Darn right it does.
Philip Kelley
A: 

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.

HLGEM
Yea, that is what I need ^)
abatishchev
+4  A: 

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.

Stuart Ainsworth
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
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
+2  A: 

That's about the limit for control structures in T-SQL, along with GOTO and WHILE.

Cade Roux