Create a table dynamically using a stored procedure
Posted by: Remi Grandsire
Date: March 16, 2011 08:26PM

I guess I'm lost, I tried everywhere to find an answer and thought that I got it but nope.
I created a stored procedure to check if a table exists and if it doesn't then create it so I can insert records.
The SP compile successfully but when I call it no matter what I try I was never able to use a dynamic name for the table.
Here's the code:
DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_tableExist` $$
CREATE PROCEDURE `sp_tableExist`(in zTable varchar(13))
begin
declare zResult varchar(13) default '';
SELECT table_name into zResult
FROM information_schema.`TABLES`
WHERE table_schema = 'pc_jetprint'
AND table_name = zTable;
if zResult='' then
set @sql2=concat('create table ', @ztable, ' (
`Piece_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`JobName` varchar(13) NOT NULL,
`UPSRec` varchar(15) NOT NULL,
`TrackNum` varchar(18) NOT NULL,
`ShipTo` varchar(35) DEFAULT NULL,
`Add1` varchar(35) DEFAULT NULL,
`Add2` varchar(35) DEFAULT NULL,
`Add3` varchar(35) DEFAULT NULL,
`City` varchar(30) DEFAULT NULL,
`State` varchar(5) DEFAULT NULL,
`Zip` varchar(10) DEFAULT NULL,
`Country` varchar(2) DEFAULT NULL,
`FinalDest` varchar(3) DEFAULT ''NP'',
`DateTime` varchar(20) DEFAULT ' ',
PRIMARY KEY (`Piece_ID`)) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=latin1;');
prepare stmt from @sql2;
execute stmt;
drop prepare stmt;
end if;
end $$
DELIMITER ;

I'm sure it is something stupid!!

And here is the error I get:
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 'NULL' at line 1

Thanks for any help!!

Remi

Options: ReplyQuote


Subject
Written By
Posted
Create a table dynamically using a stored procedure
March 16, 2011 08:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.