SQL Server 2008: CASE vs IF-ELSE-IF vs GOTO

Posted by Saharsh Shah on Stack Overflow See other posts from Stack Overflow or by Saharsh Shah
Published on 2013-06-29T07:13:49Z Indexed on 2013/06/29 10:21 UTC
Read the original article Hit count: 361

Filed under:
|
|
|
|

I have some rules in my application and I have written the business logic of that rules in my procedure. At the time of creation of procedure I came to know that CASE statement won't work in my scenario. So I have tried two ways to perform same operations (using IF-ELSE-IF or GOTO) shown as below.

Method 1 Using IF-ELSE-IF conditions:

DECLARE @V_RuleId SMALLINT;

IF (@V_RuleId = 1) 
BEGIN 
    /*My business logic*/
END
ELSE IF (@V_RuleId = 2) 
BEGIN 
    /*My business logic*/
END
ELSE IF (@V_RuleId = 3) 
BEGIN 
    /*My business logic*/
END
/* 
...
...
...
...*/
ELSE IF (@V_RuleId = 19) 
BEGIN 
    /*My business logic*/
END
ELSE IF (@V_RuleId = 20) 
BEGIN 
    /*My business logic*/
END

Method 2 Using GOTO statement:

DECLARE @V_RuleId SMALLINT, @V_Temp VARCHAR(100);

SET @V_Temp = 'GOTO RULE' + CONVERT(VARCHAR, @V_RuleId);
EXECUTE sp_executesql @V_Temp;

RULE1: 
BEGIN 
    /*My business logic*/
END

RULE2: 
BEGIN 
    /*My business logic*/
END

RULE3: 
BEGIN 
    /*My business logic*/
END

/* 
...
...
...
...*/

RULE19: 
BEGIN 
    /*My business logic*/
END

RULE20: 
BEGIN 
    /*My business logic*/
END

Today I have 20 rules. It can be increase to any number in future. If I can able to use CASE statement then I have not any problem with performance, but I can't do that so I am worried about the performance of my procedure.

Also one thing to be noticed that this procedure will execute very frequently by application.

My questions are:

Is there any way to use CASE statement in my procedure? 
If not, which method is best to use in my procedure to improve the performance of my code?

Thanks in advance...

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server