Solved

the case of CASE .... WHEN in T-SQL

Posted on 2006-10-31
7
1,081 Views
Last Modified: 2008-02-01
hi,

i just thought my  stored procedure could be more reable if the series of IF BEGIN..... END is replaced with  simple case statement  CASE ...... WHEN THEN  ; tried to transform it but my little experience with T-SQL could not help! herebelow is copy of the procedure, can u help to debug it? is anyone has a  better alternative?

thanks
-----------------------------------------------------------
 IF EXISTS(SELECT name FROM sysobjects where name='categoriesProc' and type='p')
       DROP PROC categoriesProc
 GO
CREATE PROC categoriesProc @action varchar(20), @code TINYINT=0, @category varchar(30),@descriptions varchar(100)=null
                           AS
                           
                            DECLARE @ErrorCode int
                           
                           CASE @action
                               
                                    WHEN 'AddNewRecord' THEN
                                                           INSERT INTO categories(category, descriptions)
                                                            VALUES (@category,@descriptions)
                                                              SET @ErrorCode=@@error
                                                               IF @ErrorCode <> 0
                                                                return(-100)
                                     WHEN 'EditRecord' THEN
                                                            UPDATE categories SET category=@category,
                                                            descriptions=@descriptions where @cat_id=@code
                                                              SET @ErrorCode=@@error
                                                              IF @ErrorCode <> 0
                                                                return(-101)
                                     WHEN 'DeleteRecord'  THEN
                                                               DELETE FROM categories where cat_id=@code
                                                               SET @ErrorCode=@@error
                                                              IF @ErrorCode <> 0
                                                                return(-102)
                                     ELSE  RETURN(-103)
                              END
                                                                                       
           GO
0
Comment
Question by:winklez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
7 Comments
 
LVL 15

Expert Comment

by:MNelson831
ID: 17843596
I think you are confusing CASE in t-sql with SELECT CASE in vb
0
 
LVL 15

Accepted Solution

by:
MNelson831 earned 75 total points
ID: 17843604
in T-SQL Case is part of a select statement like this:


Select
     Case
          When Field1 = 5 then 'Yes'
          When Field1 = 2 then 'No'
          Else 'Maybe'
     End
From
     MyTableName

I think that for your purposes you are going to need to stick with the If -Begin-End frunctions
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 17843671
I have been trying to re-write this proc as dynamic SQL generated using Select @SQL = Case When etc, but it is at least 8 times more complicated than the If-Begin-End method.

You can remove the DECLARE @ErrorCode int and the SET @ErrorCode=@@error portions by altering this:

                                                              SET @ErrorCode=@@error
                                                              IF @ErrorCode <> 0
                                                                return(-102)

to this:

If @@Error <> 0 return(-102)
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:winklez
ID: 17843893
>>> You can remove the DECLARE @ErrorCode int and the SET @ErrorCode=@@error portions by altering this:

                                                              SET @ErrorCode=@@error
                                                              IF @ErrorCode <> 0
                                                                return(-102)

to this:

If @@Error <> 0 return(-102)

u are right it makes the program a little bit short and quite readble but i  think the two, do the same job!
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 17844433
I thought your whole point was to make it shorter, cleare and more readable.....  Yes they do the same thing.
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 17891450
Just because the answer isn't what you wanted to hear doesn't mean that it was a bad answer.  Rather than accept an answer that you feel isn't good enough you should post a question in the communty support forum (where questions are free) and ask the moderators to close this question and refund your points.

At the VERY least you could have asked for more clarification from me prior to issuing the lowest grade possible for my answer.

Seems rather obvious to me that none of the other experts had any better suggestions for you.
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

734 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