1

I'm trying to use a case statement to update a column based on the value of another column. My table called 'rma' in MySQL is as follows:

ID    |    rma_number    |     serial_number     |    ref_status_id
1     |       9898       |         123456        |          19
2     |       7869       |         098768        |           3

Here is my stored procedure:

CREATE DEFINER=`admin`@`localhost` PROCEDURE `delete_update_rma`(in selectedID int, in selectedRMAID int)
begin

declare rmaStatus int(5);

select ref_status_id into rmaStatus
from rma
where id = selectedRMAID;

case rmaStatus
when 19 then
set ref_status_id = 0;

end case;

delete from dropbox where id = selectedID;

end

When I try to save to create the procedure I receive the error #1193 - Unknown system variable 'ref_status_id'.

Can anybody help me identify what may be wrong with my stored procedure?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
brandozz
  • 1,059
  • 6
  • 20
  • 38
  • Are you sure `ref_status_id` is a declared variable? If you are wanting to update a row, you may have to find another way to do that part... – summea Jan 08 '14 at 02:47

3 Answers3

1

Try this: 

CREATE DEFINER=`admin`@`localhost` PROCEDURE `delete_update_rma`(IN selectedID INT, IN selectedRMAID INT)
BEGIN

UPDATE rma
SET ref_status_id = 
  CASE ref_status_id
    WHEN 19 THEN 0 
    WHEN 3 THEN 2
    ELSE ref_status_id
  END
WHERE id = selectedRMAID;

DELETE FROM dropbox WHERE id = selectedID;

END

Check the SQL FIDDLE DEMO

OUTPUT

| ID | RMA_NUMBER | SERIAL_NUMBER | REF_STATUS_ID |
|----|------------|---------------|---------------|
|  1 |      12345 |         67898 |             0 |
|  2 |      45645 |         89056 |             2 |
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • this is working in the fiddle but for some reason it's not updating the value of ref_status_id properly. No matter what the original value of ref_status_id is, the updated value is 0. – brandozz Jan 09 '14 at 04:07
  • @brandozz You're most welcome... Can you explain the problem with example? – Saharsh Shah Jan 09 '14 at 04:10
  • In the example that you have in the SQL FIDDDLE DEMO, if I add "where id = 2" for example, the procedure doesn't work, but if I add "where id = 1" the procedure changes the ref_status_id to 0 as expected. – brandozz Jan 09 '14 at 18:49
  • My bad, this is only happening on my end, not in your fiddle. I did some more testing. I'm not sure what the problem is on my end. – brandozz Jan 10 '14 at 02:11
0

The case syntax is correct, however you cannot change a database field inside a case statement, you need to use an update statement for that. 

UPDATE rma 
SET    ref_status_id = 0 
WHERE  ref_status_id = 19 
       AND id = selectedrmaid;

You can only change local vars inside this kind of case statement.

Yours is a typical case of mixing procedural and query logic, a common error when doing stored procedures.

Hawk
  • 5,060
  • 12
  • 49
  • 74
Johan
  • 74,508
  • 24
  • 191
  • 319
-1

The line ==>

set ref_status_id = 0;

Is causing the error. You are treating set ref_status_id as a variable when its a column in the rma table.

What are you trying to do in that case statement? Since the checking for a hard coded values of 19 doesn't seem to align with what you are trying to do.

robnick
  • 1,720
  • 17
  • 27