I want to call the mysql stored procedure to create a table. The table name is passed through the in parameter. CREATEPROCEDUREfriendList (intnamevarchar (20) BEGINSET @ sqlstrCONCAT (CREATETABLEifnotexists, tname, (useridintunsigned, phoneVARCHAR (20) NOTNULL, nam
I want to call the mysql stored procedure to create a table. The table name is passed through the in parameter. Create procedure friendList (in tname varchar (20) begin set @ sqlstr = CONCAT ('create TABLE if not exists', tname, '(userid int unsigned, phone VARCHAR (20) not null, nam
Mysql write Stored Procedure Problems
I want to call the mysql stored procedure to create a table. The table name is passed through the in parameter.
Create procedure friendList (in tname varchar (20 ))
BEGIN
SET @ sqlstr = CONCAT ('create TABLE if not exists ',
Tname,
'(
Userid int unsigned,
Phone VARCHAR (20) not null,
Name VARCHAR (30) not null,
Birth int,
Nick VARCHAR (15 ),
Impression varchar (50)
) ENGINE = InnoDB default charset = utf8 ');
Prepare stmt from @ sqlstr;
Execute stmt;
END;
If you run the command in windows cmd, an error is always reported. 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
It turns out that mysql executed in cmd is equivalent to commit, so this error will always be reported.
The statement Terminator should be changed to "//" for this instance.
So:
Delimiter //
Create procedure friendList (in tname varchar (20 ))
BEGIN
SET @ sqlstr = CONCAT ('create TABLE if not exists ',
Tname,
'(
Userid int unsigned,
Phone VARCHAR (20) not null,
Name VARCHAR (30) not null,
Birth int,
Nick VARCHAR (15 ),
Impression varchar (50)
) ENGINE = InnoDB default charset = utf8 ');
Prepare stmt from @ sqlstr;
Execute stmt;
END;
//