case statement error 1

[mp89]

Can anybody tell me what is wrong with my case statement? The updates/inserts work fine on their own, but I must be making some syntax error in building up the case statement.


case @destinterest
when '40' then
begin
if @longdescription between 10 and 20000
begin
update division
set billtype = 1, datestamp = convert(char(10), getdate(), 103), rollover = 0, minutes = @longdescription, package = @package
where cug = cast(@cug as int) and subcug = cast(@subcug as int)

select @err = @@error, @rowcount = @@rowcount

if (@err <> 0) or (@rowcount = 0)
begin
goto on_error
end

update rental_log
set productnum = @package, selldescription = @description, billtype = 1
where cug = @cug and sub_cug = @subcug and number = @number

select @err = @@error, @rowcount = @@rowcount

if (@err <> 0) or (@rowcount = 0)
begin
goto on_error
end

update lnxser.homelink.dbo.purple_cards
set [value] = @seconds, gossippack = @coretariff
where account_oli1 = cast(@numberlesszero as numeric)

select @err = @@error, @rowcount = @@rowcount

if (@err <> 0) or (@rowcount = 0)
begin
goto on_error
end
end
else
begin
select @err = @@error, @rowcount = @@rowcount

if (@err <> 0) or (@rowcount = 0)
begin
goto on_error
end
end
end
end

when '41' then
begin
insert into rental_log(incepttime, productnum, qty, mia, processed, selldescription, billtype)
values(convert(char(10), getdate(), 103), @package, 1, 1, 1, @description, 1)

select @err = @@error, @rowcount = @@rowcount

if (@err <> 0) or (@rowcount = 0)
begin
goto on_error
end
end
else
begin
select @err = @@error, @rowcount = @@rowcount

if (@err <> 0) or (@rowcount = 0)
begin
goto on_error
end
end
end

Cheers,

Mike

 

[hmckillop]

Thats not how case works, its built to be used inside SQL statements as opposed to a conditional statement such as IF.
e.g.

SELECT   Category = 
      CASE type
         WHEN 'popular_comp' THEN 'Popular Computing'
         WHEN 'mod_cook' THEN 'Modern Cooking'
         WHEN 'business' THEN 'Business'
         WHEN 'psychology' THEN 'Psychology'
         WHEN 'trad_cook' THEN 'Traditional Cooking'
         ELSE 'Not yet categorized'
      END,
   CAST(title AS varchar(25)) AS 'Shortened Title',
   price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

Stick to using IF statements for the above example i.e.:

IF @destinterest='40' 
        begin
            if @longdescription between 10 and 20000
            begin
                update division
                set billtype = 1, datestamp = convert(char(10), getdate(), 103), rollover = 0, minutes = @longdescription, package = @package
                where cug = cast(@cug as int) and subcug = cast(@subcug as int)
                
                select @err = @@error, @rowcount = @@rowcount 

                if (@err <> 0) or (@rowcount = 0)
                begin
                    goto on_error           
                end

                update rental_log
                set productnum = @package, selldescription = @description, billtype = 1
                where cug = @cug and sub_cug = @subcug and number = @number
        
                select @err = @@error, @rowcount = @@rowcount 
        
                if (@err <> 0) or (@rowcount = 0)
                begin
                    goto on_error           
                end

                update lnxser.homelink.dbo.purple_cards
                set [value] = @seconds, gossippack = @coretariff
                where account_oli1 = cast(@numberlesszero as numeric)
        
                select @err = @@error, @rowcount = @@rowcount 
        
                if (@err <> 0) or (@rowcount = 0)
                begin
                    goto on_error           
                end
            end
            else
                begin
                    select @err = @@error, @rowcount = @@rowcount 
            
                    if (@err <> 0) or (@rowcount = 0)
                    begin
                        goto on_error           
                    end
                end    
            end
        end

ELSE IF @destinterest='41' 
        begin
            insert into rental_log(incepttime, productnum, qty, mia, processed, selldescription, billtype)
            values(convert(char(10), getdate(), 103), @package, 1, 1, 1, @description, 1)
        
            select @err = @@error, @rowcount = @@rowcount 
        
            if (@err <> 0) or (@rowcount = 0)
            begin
                goto on_error           
            end
        end
    else
        begin
            select @err = @@error, @rowcount = @@rowcount 
        
            if (@err <> 0) or (@rowcount = 0)
            begin
                goto on_error           
            end
        end
    end

"I'm living so far beyond my income that we may almost be said to be living apart