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.
This question is related to
sql
sql-server
tsql
sql-server-2008
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.
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.
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
The Transact-SQL control-of-flow language keywords are:
BEGIN
...END
BREAK
CONTINUE
GOTO
label
IF
...ELSE
RETURN
THROW
TRY
...CATCH
WAITFOR
WHILE
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.
Source: Stackoverflow.com