Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1091
  • Last Modified:

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

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
winklez
Asked:
winklez
  • 5
1 Solution
 
MNelson831Commented:
I think you are confusing CASE in t-sql with SELECT CASE in vb
0
 
MNelson831Commented:
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
 
MNelson831Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
winklezAuthor Commented:
>>> 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
 
MNelson831Commented:
I thought your whole point was to make it shorter, cleare and more readable.....  Yes they do the same thing.
0
 
MNelson831Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now