Solved

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

Posted on 2006-10-31
7
1,076 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
  • 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

747 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

12 Experts available now in Live!

Get 1:1 Help Now