The Case Statement

[FontanaS]

I have the following case statement:

ON_ERROR:
CASE @Err
WHEN 0 THEN INSERT INTO tErrorLog_BU (ErrNumber,ErrDescription) VALUES (@Err,'tErrorLog Truncate Error') ROLLBACK TRANSACTION RETURN(0)

If there is an error in the sql statements in the beginning, then it goes to ON_ERROR and goes through a case statement.

@Err is a int that is assigned after every sql statement in the beginning and is an integer (int)

I keep getting an error that there is incorrect syntax near the word CASE

Can you tell me what i am doing wrong?

THANKS

 

[foxdev]

For one thing, a CASE statement requires an END; but if there is only one condition that you are testing for, why not use IF ? Robert Bradley
Support operation moo!
Visit the OpCow page

http://www.agrainofhope.org/opcow.shtml

to help in this cause

 

[FontanaS]

I do have an end after the else part of the case statement.

I have about 50 different when statements

case @Err
when 0 then ...........
when 1 then ...........
when 2 then ...........
.....................
when 50 then ..........
else return(100)
end

 

[smasaitis]

I think the following code would be the best way to trap for your different errors:

if @Err = 0
begin -- begin/end used if there is more than
-- one statement to perform after the if
insert into tErrorLog_BU (ErrNumber, ErrDescription)
values (@Err, 'tErrorLog Truncate Error')
end

else if @Err = 1
begin
insert into tErrorLog_BU (ErrNumber, ErrDescription)
values (@Err, 'Some Error')
end

else if @Err = 2
begin
insert into tErrorLog_BU (ErrNumber, ErrDescription)
values (@Err, 'Some Other Error')
end

.
.
.

else
return(100)

if @Err > 0
begin
rollback transaction
return(0)
end

Hope this code helps.
Steve

 

[ludek]

The SQL Server use of the keyword CASE is different from what one would excpect from Pascal, C, or other prog languages. It is a conditional function, and not a "control of flow" statement. "IF .. ELSE .." will have to be usd for what you want to do.

Ludek