SQL Server 2008: CASE vs IF-ELSE-IF vs GOTO
- by Saharsh Shah
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...
