MySQL stored procedure with in and out parameters and a small example of how to call PHP and PB

##The simplest example:

mysql> DELIMITER $$
mysql> USE test $$
Database changed
mysql> DROP PROCEDURE IF EXISTS `sp_add`$$
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE sp_add(a INT, b INT,OUT c INT)
    -> BEGIN 
    -> SET c=a+ b;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL sp_add (1,2,@c);
Query OK, 0 rows affected (0.00 sec)mysql> SELECT @c;
+------+
| @c   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)mysql>

A slightly more complicated example:

mysql> show create table t_BillNo;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                        |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_BillNo | CREATE TABLE `t_billno` (
  `SaleNo` bigint(20) DEFAULT NULL,
  `bmh` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t_BillNo;
+--------+------+
| SaleNo | bmh  |
+--------+------+
|      1 | 2    |
|      4 | 3    |
|      4 | 5    |
|      7 | 7    |
|     12 | 8    |
+--------+------+
5 rows in set (0.00 sec)

mysql> 
mysql> DELIMITER $$
mysql> USE test $$
Database changed
mysql> DROP PROCEDURE IF EXISTS `sp_GetMaxNumber`$$
Query OK, 0 rows affected (0.01 sec)

DELIMITER $$
USE test $$
DROP PROCEDURE IF EXISTS `sp_GetMaxNumber`$$
CREATE PROCEDURE sp_GetMaxNumber (IN v_bmh VARCHAR(6), OUT v_MaxNo INT)
BEGIN
	START TRANSACTION;
	UPDATE t_BillNo
	SET SaleNo = IFNULL(SaleNo,0)+1 
	WHERE bmh = v_bmh;
	IF @@error_count = 0 THEN
		BEGIN
		  SELECT Saleno INTO v_MaxNo FROM t_BillNo WHERE bmh = v_bmh;
		COMMIT;
		END;
	ELSE
		BEGIN
		     ROLLBACK;
		     SET v_MaxNo = 0;
		END;
	END IF;
END$$
DELIMITER ;

mysql> CREATE PROCEDURE sp_GetMaxNumber (IN v_bmh VARCHAR(6), OUT v_MaxNo INT)
    -> BEGIN
    -> START TRANSACTION;
    -> UPDATE t_BillNo
    -> SET SaleNo = IFNULL(SaleNo,0)+1 
    -> WHERE bmh = v_bmh;
    -> IF @@error_count = 0 THEN
    -> BEGIN
    ->   SELECT Saleno INTO v_MaxNo FROM t_BillNo WHERE bmh = v_bmh;
    -> COMMIT;
    -> END;
    -> ELSE
    -> BEGIN
    ->      ROLLBACK;
    ->      SET v_MaxNo = 0;
    -> END;
    -> END IF;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> 

mysql> call sp_GetMaxNumber(8,@v_MaxNo);
Query OK, 0 rows affected (0.00 sec)

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

# How to call MySQL's stored procedure in php? I have not tested it. I have borrowed examples from friends, as follows:

$sql = "call test.sp_GetMaxNumber(8,@c);";
mysql_query($sql);//调用sp_GetMaxNumber的存储过程
$result = mysql_query('select @c;');
$array = mysql_fetch_array($result);
echo &#39;<pre class="brush:php;toolbar:false">&#39;;print_r($array);

But when called in PB, the error is reported as follows (example from a netizen on itpub). Friends who have similar experiences, please share your experience: In CB_1 of the PB W_MAIN form. In the CLICK event:

string ls_bmh, ls_errtext
int li_maxno
ls_bmh = '0901'
sp_GetMaxNumber(ls_bmh, li_maxno)
ls_errtext = sqca.sqlerrtext
messagebox('Error',string(li_maxno)+' ' +sqlca.sqlerrtext)

But the PB display shows an error:

0 SQLSTATE = 37000
[MYSQL] [ODBC 5.1 DRIVER]YOU HAVE AN ERROR IN YOUR SQL SYNTAX;CHECK THE MANUAL THAT CORRESPONDS TO YOUR MYSQL SERVER VERSION FOR THE RIGHT SYNTAX TO USE 
NEAR '?=CALL SP_GetMaxNumber(0,_GBK'0901') AT LINE 1.

The above is the MySQL stored procedure with in and out parameters and how to call PHP and PB For the content of small examples, please pay attention to the PHP Chinese website (www.php.cn) for more related content!