?
Solved

SQL Complicated CASE for a Stored PROC

Posted on 2012-08-20
4
Medium Priority
?
459 Views
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
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
0
Comment
Question by:hainansyndrome
4 Comments
 
LVL 13

Accepted Solution

by:
LIONKING earned 400 total points
ID: 38313770
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
 

Author Comment

by:hainansyndrome
ID: 38313796
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
 
LVL 9

Expert Comment

by:keyu
ID: 38315073
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38316672
I'm not sure I see how changing from an IF to a CASE is going to help performance.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Screencast - Getting to Know the Pipeline

829 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