Create a table dynamically using a stored procedure
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