Re: Create a table dynamically using a stored procedure

[Remi Grandsire]

Oh well I got it, I know it was something stupid, the NULL referred in the error message is the table name. So instead of @zTable I use zTable and frame it with `
Then there was another error this time related to the default value of the last table field. Instead of ' ' I use '' '' and it worked just fine.

See below:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_tableExist` $$
CREATE DEFINER=`root`@`localhost` 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 `pc_jetprint`.`',ztable,'` (
`Piece_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Operator` varchar(50) Default NULL,
`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=1 DEFAULT CHARSET=latin1;');
prepare stmt from @sql2;
execute stmt;
drop prepare stmt;
end if;
/*

*/
end $$

DELIMITER ;