[SOLVED] mysql stored procedure help

rootaccess · 10-18-2012, 02:10 PM

Hi. I'm basically trying to create a stored procedure with parameters where I can pass off either 'Y' OR 'N' (but not both at the same time) as parameters to change values in a table.

I have a table called procedures.
In there 4 rows are listed.
If I pass 'Y' as a parameter, it needs to update 2 rows; for mike and tom.
If I pass 'N' as a parameter, it updates the other 2 rows; for dick and harry.

Here is what I have listed and I tried playing with so many different ways and have not succeeded.

Code:

create procedure yn (IN x char(1))
BEGIN
SET x = Y or N;
IF x = Y THEN
UPDATE procedures SET number1 = 3, number2 = 4, number3 = 5 WHERE Name='mike';
UPDATE procedures SET number1 = 3, number2 = 2, number3 = 1 WHERE Name='tom';
END IF;
IF x = N THEN
UPDATE procedures SET number1 = 5, number2 = 6, number3 = 7 WHERE Name='dick';
UPDATE procedures SET number1 = 22, number2 = 33, number3 = 44 WHERE Name='harry';
END IF;
END; //

If someone can please help me, I would appreciate it.

Thanks,
Shawn

rootaccess · 10-18-2012, 02:18 PM

I figured it out myself. For anyone curious for some odd strange reason, here is the stored procedure as well as how to call it

Code:

create procedure yn (IN x char(1))
BEGIN
IF x = 'Y' THEN
UPDATE procedures SET number1 = 3, number2 = 4, number3 = 5 WHERE Name='mike';
UPDATE procedures SET number1 = 3, number2 = 2, number3 = 1 WHERE Name='tom';
END IF;
IF x = 'N' THEN
UPDATE procedures SET number1 = 5, number2 = 6, number3 = 7 WHERE Name='dick';
UPDATE procedures SET number1 = 22, number2 = 33, number3 = 44 WHERE Name='harry';
END IF;
END; //

Code:

call yn ('Y');