asked on

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

MNelson831

I think you are confusing CASE in t-sql with SELECT CASE in vb

ASKER CERTIFIED SOLUTION

MNelson831

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MNelson831

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)

winklez

ASKER

>>> 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!

MNelson831

I thought your whole point was to make it shorter, cleare and more readable..... Yes they do the same thing.

MNelson831

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.