have you tried sugar CRM ?

content management in plain english

How to Empty or Truncate Your MySQL Database Table Tutorial

one-to-many relationship

reserved sql keyword as column name

Junction Table

stored procedure examples in MySQl 5.0

a stored procedure is a set of pre compiled SQL routines physically stored on the database.


create procedure proc_name([parameter[,...]])
[characteristic ...] routine_body

parameter:
[IN|OUT|INOUT] param_name type

type:
any valid mysql data type

characteristic:
LANGUAGE SQL
| [not] DETERMINISTIC
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT 'string'

routine_body:
valid sql procedure statements
-------------------------------------------------------------------------------------------

parameter types in stored procedures:-

IN: the default. this parameter is passed to the procedure and can be changed inside the procedure, but remains changed outside.

OUT: no value is supplied to the procedure(it is assumed to be null), but it can be modified inside the procedure and is available outside the procedure

INOUT: the characteristics of both IN and OUT parameters. A value can be passed to the procedure, modified there as well as passed back again.

Ex:-

mysql> delimiter //
mysql> create procedure displaycustomer (OUT Param1 INT)
-> begin
-> select count(*) into Param1 from CustomerMaster;
-> end
-> //
Query OK, 0 rows affected (0.01 sec)

mysql> call displaycustomer(@noofc);
Query OK, 0 rows affected (0.05 sec)

mysql> select @noofc;
+--------+
| @noofc |
+--------+
| 12 |
+--------+
1 row in set (0.00 sec)


Ex:-
mysql> create procedure square(INOUT P INT) set p=p*p;
Query OK, 0 rows affected (0.00 sec)

mysql> set @Number=10;
Query OK, 0 rows affected (0.00 sec)

mysql> call square(@Number);
Query OK, 0 rows affected (0.00 sec)

mysql> select @Number;
+---------+
| @Number |
+---------+
| 100 |
+---------+
1 row in set (0.00 sec)

------------------------------------------------------------------------------------------------------------

mysql> create procedure procupdate(trnid VARCHAR(6), maxamt INT)
begin
declare num INT;
start transaction;
update TransactionDetail
set productcost=productcost+productcost*0.5
where transactionid=trnid;

update TransactionMaster
set amount=(select sum(productcost) from TransactionDetail where transactionid=trnid)
where transactionid=trnid;

select amount into @num from TransactionMaster
where transactionid=trnid;

IF @num >maxamt then
rollback;
select 'transaction amount more than transaction limit';
select 'action performed rollback';
else
commit;
select 'transaction amount within maximum limit';
select 'action performed commit';
END IF;

end
//
Query OK, 0 rows affected (0.03 sec)
------------------------------------------------------------------------------------------------------------

mysql> create procedure procinssp()
-> begin
-> declare trnamt INT;
-> START TRANSACTION;
-> savepoint spins;
-> insert into TransactionDetail values('TRN013','PRD004',10000);
-> insert into TransactionDetail values('TRN013','PRD008',1000);
-> select sum(productcost) into @trnamt from TransactionDetail
-> where transactionid='TRN013';
->
-> IF @trnamt < 20000 THEN
-> ROLLBACK;
-> select 'sum of product cost < 20000';
-> select 'action performed: Rollback ';
-> END IF;
->
-> IF @trnamt > 30000 and @trnamt < 60000 THEN
-> ROLLBACK TO savepoint spins;
-> select 'sum of product cost between 30000 AND 60000';
-> select 'action performed: Rollback to a save point ';
-> END IF;
->
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)


How to drop a procedure ?

drop procedure [IF EXISTS]