Solved

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

Posted on 2006-10-31
7
1,078 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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