SQL Complicated CASE for a Stored PROC

I have inherited a complicated Stored Procedure, in sql 2000


in it there are about 80 times it does a
if @step= 'Some value'
I want to rewrite it using a case statement but i am having no luck, remember This is a complicated Stored PROC this is just a sample, the real thing if over 1200 lines

I have tried
Select Case @step
WHEN 'L' THEN
INSERT INTO track_log
(userID, task_ID, wkst, step, lookup_id)
VALUES (@user_name, 1, HOST_NAME(), '8130a', @epn_no)

WHEN 'DEL' THEN  --Delete EPN_No from custom tables
BEGIN
   EXEC [usp_8130_delete] @_epn_no = @epn_no
   BEGIN TRAN

   INSERT INTO track_log (userID, task_ID, wkst, step, lookup_id)
   VALUES (@user_name, 9, HOST_NAME (),'DEL',@epn_no)

   --------------------------------------ERROR HANDLING
   SELECT @ErrNum = @@ERROR

   IF @ErrNum <> 0
      BEGIN
         GOTO sqlerror
      END
   --------------------------------------ERROR HANDLING
   COMMIT
END


END
hainansyndromeAsked:
Who is Participating?
 
LIONKINGConnect With a Mentor Commented:
The case statement in SQL doesn't work like in a regular programming language. It's not meant to replace the "if" statement for "structured" programming inside SQL, it's intention is to evaluate an expression inside a T-SQL statement and return a value based on a condition.

Here's a (way) better explanation of the Case statement:

Link
0
 
hainansyndromeAuthor Commented:
That is what I figured. I have spent weeks rewriting VB.net and java script IF statements into Case and Switches tryign to get better performance then I saw this monster of a stored proc
0
 
keyuCommented:
hi,

if you want to use "case..when.."  statement only instead of "IF..Else.." then you must use simple "select..Case"

Select Case Hour(Now())
  Case 0
    'Do whatever needs to be done at midnight
  Case 1
    'Do whatever needs to be done at 1:00 am
  Case 2
    ...
  ...
End Select



for more information refer below link might helps you..

http://www.4guysfromrolla.com/webtech/102704-1.shtml
0
 
Scott PletcherSenior DBACommented:
I'm not sure I see how changing from an IF to a CASE is going to help performance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.