SQL Server Performance Forum – Threads Archive

Using CASE statement to control flow in procedure

Is it possible to use a CASE statement to control process flow within a procedure? I have tried the following but it will not compile. Thanks, Jethrow CREATE PROC test
(
@input1VARCHAR(100),
)
AS SET NOCOUNT OFF CASE @input1
WHEN ‘process_flow_1’ THEN
a series of processing such as set, open cursor, insert row, etc.
WHEN ‘proces_flow_2’ THEN
a different series of processing such as set, open cursor, insert row, etc.
ELSE
yet another different series of processing such as set, open cursor, insert row, etc.
END SET NOCOUNT OFF RETURN 0
GO

---

I dont think it is possible to use case as a control of flow statement- from BOL:
I believe you will have to use IF/ELSE IF/ELSE statements Ben

---

Ben is correct. Also, if you don’t use functions between the IF statements, you are going to have a lot of recompile and performance issues. You need to be aware of that. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Ben and Derrick, Thanks for the replies and info. Derrick, can you elaborate on why there would be a lot of recompiles and performance issues if functions are not used within if statements. Are you saying that the following will have less compile and performs better: if a > b
function d (where function d insert into table a)
else
function e (where function e insert into table b) instead of the following which will have lots of compiles and performs poorer: if a > b
insert into table a
else
insert into table b I thought the reason to use a procedure was so that recompile does not occur. Thanks,
Jethro

---

The general idea is that if you have very dissimilar queries firing for the various options in your procedure, the procedure is more likely to get recompiled. If you create separate stored procedures for those dissimilar queries, each of which is less likely to be recompiled, then the master procedure is also less likely to be recompiled. Personally, I would look into using EXEC dbo.sp_ExecuteSQL set up with proper parameters for those queries, rather than using additional procedures, but that’s just me.

---

]]>