Solved

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

Posted on 2006-10-31
7
1,080 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 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