MySQL titbits .....................1

Today my friend asked how to create procedures in MySQL ... i just created this small example to get her a clue.

___________________________

procedure definition.
___________________________


DELIMITER $$
DROP PROCEDURE IF EXISTS myProc$$
CREATE PROCEDURE myProc()
BEGIN
DECLARE myvar char(10);
DECLARE len, ctr int DEFAULT 1;
SELECT count(*) INTO len FROM ss_temp ;

WHILE ctr<=len DO
select ctr;
SELECT name INTO myvar FROM ss_temp WHERE sub_id= ctr;

IF myvar ='songs' THEN
update ss_temp set name='Jokes' where sub_id=ctr;
ELSE
update ss_temp set name='songs' where sub_id=ctr;
END IF;
SET ctr = ctr + 1;
END WHILE;

END$$
DELIMITER ;

__________________________

check if procedure exists

SHOW PROCEDURE STATUS;
________________________

call procedure

call myProc();

_______________________