CODEWITHSUNDEEP
mysqlstored-procedures
Huckleberry
Huckleberry

Reputation: 31

How to pass variables into MySql Procedure creation

I want to be able to create some stored Procedures in different databases - so I want to be able to pass in the database name into the stored procedure creation statement. 

SET @SourceDBName='dev';
DELIMITER //
CREATE PROCEDURE test ()
BEGIN 
    USE @SourceDBName;
    SELECT * FROM agreed_relation;
END //
DELIMITER ;

How can I pass @SourceDBName into the CREATE PROCEDURE statement?

Upvotes: 1

Views: 1427

Answers (2)

wchiquito
wchiquito

Reputation: 16569

14.1.16 CREATE PROCEDURE and CREATE FUNCTION Syntax

...

USE statements within stored routines are not permitted. When a routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates). The causes the routine to have the given default database while it executes. References to objects in databases other than the routine default database should be qualified with the appropriate database name.

...

An option to create the stored procedure in different databases is:

FIle: /path/to/file/myProcedure.sql

DROP PROCEDURE IF EXISTS `test`;

DELIMITER //

CREATE PROCEDURE `test`()
BEGIN 
  SELECT * FROM `agreed_relation`;
END//

DELIMITER ;

MySQL Command-Line:

mysql> USE `dev`;
Database changed

mysql> \. /path/to/file/myProcedure.sql
Query OK, 0 rows affected (0.00 sec)

mysql> USE `db1`;
Database changed

mysql> \. /path/to/file/myProcedure.sql
Query OK, 0 rows affected (0.00 sec)

mysql> USE `db2`;
Database changed

mysql> \. /path/to/file/myProcedure.sql
Query OK, 0 rows affected (0.00 sec)

mysql> USE `dev`;
Database changed

mysql> CALL `test`;
Empty set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> USE `db1`;
Database changed

mysql> CALL `test`;
Empty set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> USE `db2`;
Database changed

mysql> CALL `test`;
Empty set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

You can also create the stored procedure in a single database and pass the database name as a parameter to qualified the required objects and use 14.5 Prepared SQL Statement Syntax to execute it:

mysql> USE `dev`;
Database changed

mysql> DROP PROCEDURE IF EXISTS `test`;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> CREATE PROCEDURE `test`(`db_name` VARCHAR(64))
    -> BEGIN
    ->   SET `db_name` := TRIM('\'' FROM QUOTE(`db_name`));
    ->   SET @`query` := CONCAT('SELECT * FROM `', `db_name`, '`.`agreed_relation`');
    ->   PREPARE `stmt` FROM @`query`;
    ->   EXECUTE `stmt`;
    ->   DEALLOCATE PREPARE `stmt`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `test`('dev');
Empty set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL `test`('db1');
Empty set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL `test`('db2');
Empty set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Upvotes: 0

Daniel
Daniel

Reputation: 21

to pass in the database name into a stored procedure you must declare it in the procedure like this:

DELIMITER //
CREATE PROCEDURE test (IN idbname VARCHAR(20))
BEGIN 
    SELECT * FROM agreed_relation WHERE dbname = idbname;
END //
DELIMITER ;

After you create a Table like this where 'dbname' field it's the one your procedure will call:

create table agreed_relation
(
dbname varchar(30) not null
);

After insert some values you call the procedure and pass any dbname to the SELECT statament:

insert into agreed_relation(dbname) values('Oracle');
insert into agreed_relation(dbname) values('Mysql');
insert into agreed_relation(dbname) values('Mongodb');

Calling procedure and passing some value:

CALL test('Mysql');

Upvotes: 2

Related Questions