0

Sorry for long POST: Is it possible that I can call a Stored Procedures from another Stored procedure in MySQL. For example: I have two tables (test and testcomp): With the structures below:

-- Table structure for table test

CREATE TABLE IF NOT EXISTS `test` (
    `t_id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`t_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

and

-- Table structure for table testcomp

CREATE TABLE IF NOT EXISTS `testcomp` (
    `c_id` int(11) NOT NULL AUTO_INCREMENT,
    `t_id` int(4) NOT NULL,
    `place` varchar(255) NOT NULL,
    PRIMARY KEY (`c_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

Now I populated test table with:

INSERT INTO `test` (`t_id`, `name`) VALUES
    (1, 'foo'),
    (2, 'bar'),
    (3, 'ma');

and table testcomp with:

INSERT INTO `testcomp` (`c_id`, `t_id`, `place`) VALUES
    (1, 1, 'gugs'),
    (2, 2, 'nyanga'),
    (3, 1, 'gugs'),
    (4, 3, 'skom');

Now if I have 2 Procedures:

First QryTestComp:

SELECT t_id, place FROM TestComp

The one above works as the just querying normal table: But the Second One QryTestPlac, which calls the above procedure:

SELECT * FROM Test INNER JOIN QryTestComp ON Test.t_id = QryTestComp.t_id

Comes with a error:

It says Error: 1146 (42S01): Table 'mydb.qrytestcomp' doesn't exist. It not a table but a procedure.

Pointer, please.

Regards,

--Jongi

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Jongilanga GUma
  • 1

1 Answers1

5

you can't join onto a stored procedure, perhaps using views might be more suitable ?

Jon Black
  • 16,223
  • 5
  • 43
  • 42
  • Alternatively, consider selecting into a temporary table from the stored procedure, then joining on that. Views make more sense, though. – Ant Nov 05 '10 at 10:05
  • Thanks, the views work like charm, that is what I wanted! Have a great day – Jongilanga GUma Nov 05 '10 at 10:07