SQL Complicated CASE for a Stored PROC

Posted on 2012-08-20
Last Modified: 2012-08-30
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
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
   EXEC [usp_8130_delete] @_epn_no = @epn_no

   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
         GOTO sqlerror
   --------------------------------------ERROR HANDLING

Question by:hainansyndrome
    LVL 13

    Accepted Solution

    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:


    Author Comment

    That is what I figured. I have spent weeks rewriting and java script IF statements into Case and Switches tryign to get better performance then I saw this monster of a stored proc
    LVL 9

    Expert Comment


    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..
    LVL 68

    Expert Comment

    I'm not sure I see how changing from an IF to a CASE is going to help performance.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video discusses moving either the default database or any database to a new volume.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now