Hey i want to write a function in sql which insert the handover parameters. My Code:
CREATE PROCEDURE `p_add_client`(IN p_username VARCHAR(45),IN p_reach VARCHAR(45),IN p_purchase VARCHAR(45))
BEGIN
set @str = ('INSERT INTO t_', p_username,'(Purchase,Reach) VALUES (p_purchase,p_reach)');
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
but it doesnt work. I get the error Code 1064. Can anyone help me?
You are missing CONCAT():
set @str = CONCAT('INSERT INTO t_', p_username, '(Purchase, Reach) VALUES (p_purchase, p_reach)');
However, you will then get an error when you execute, because the variables are not known. So, you could write:
set @str = CONCAT('INSERT INTO t_', p_username, '(Purchase, Reach) VALUES (p_purchase, p_reach)');
prepare stmt from @str;
execute stmt using p_purchase, p_reach;
deallocate prepare stmt;
Of course, this all suggests a pretty bad database architecture. Why would you have a separate table per user, rather than rows in a single table for all users? There are some arcane reasons why this might be a solution to a real problem, but such an approach much more commonly suggests a problem with the solution architecture.
