Vfrdtyky

I'm creating a MySQL stored procedure that receives the names of two views, and performs a Union upon them into the first of the two.

I've used prepared statements to take view names as strings, so I can use this stored procedure with a wealth of other stored procedures that produce views of varying names.

The following code works as expected:

DROP view if exists test1;

DROP view if exists test2;



CREATE VIEW test1 as SELECT "Cows";

CREATE VIEW test2 as SELECT "Horses";



DROP VIEW if exists tempView;

CREATE VIEW tempView AS SELECT * FROM test1 UNION SELECT * FROM test2;

SELECT * from tempView;

However, when I execute the following code:

DELIMITER //

DROP PROCEDURE IF EXISTS SP_unionViews //

CREATE PROCEDURE SP_unionViews(IN viewname varchar(255), 

                               IN viewname2 varchar(255))

BEGIN

    DROP VIEW IF EXISTS tempView;

    SET @in1 = viewname;

    SET @in2 = viewname2;

    SET @str = 'CREATE VIEW tempView AS 

       SELECT * FROM ? UNION SELECT * FROM ?';

    PREPARE stmt FROM @str;

    EXECUTE stmt USING @in1, @in2;



    SET @str2 = 'DROP VIEW ?';

    SET @in3 = viewname;

    PREPARE stmt2 FROM @str2;

    EXECUTE stmt2 USING @in3;



    SET @str3 = 'CREATE VIEW ? AS SELECT * FROM tempView';

    PREPARE stmt3 FROM @str3;

    EXECUTE stmt3 USING @in3;



    DEALLOCATE PREPARE stmt;

    DEALLOCATE PREPARE stmt2;

    DEALLOCATE PREPARE stmt3;

END //

DELIMITER ;



DROP view if exists test1;

DROP view if exists test2;



CREATE VIEW test1 as SELECT "Cows";

CREATE VIEW test2 as SELECT "Horses";

CALL SP_unionViews(test1, test2);

SELECT * from test1;

I get the following error:

#1054 - Unknown column 'test1' in 'field list'

That would seem to indicate that this stored procedure is trying to use test1 as a column somewhere it's not intended to be one. But I can't figure out where.

UPDATE:
When edited as below, I expected the solution to work, but it gives me another error.

The new code reads:

DELIMITER //

DROP PROCEDURE IF EXISTS SP_unionViews //

CREATE PROCEDURE SP_unionViews(IN viewname varchar(255), 

                               IN viewname2 varchar(255))

BEGIN

    DROP VIEW IF EXISTS tempView;

    SET @str = CONCAT('

    CREATE VIEW tempView AS 

        SELECT * FROM ', viewname, ' UNION SELECT * FROM ', viewname2);

    PREPARE stmt FROM @str;

    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;



    SET @str2 = CONCAT('DROP VIEW ', viewname, ';');

    PREPARE stmt2 FROM @str2;

    EXECUTE stmt2;

    DEALLOCATE PREPARE stmt2;



    SET @str3 = CONCAT('CREATE VIEW ', viewname ,' AS SELECT * FROM tempView');

    PREPARE stmt3 FROM @str3;

    EXECUTE stmt3;

    DEALLOCATE PREPARE stmt3;

END //

DELIMITER ;



DROP view if exists test1;

DROP view if exists test2;



CREATE VIEW test1 as SELECT "Cows";

CREATE VIEW test2 as SELECT "Horses";

CALL SP_unionViews("test1", "test2");

SELECT * from test1;

And its error reads:

#1615 - Prepared statement needs to be re-prepared

You can't use parameters for table names in a prepared statement, so you will need to put the table names into the query string before you prepare the statement. Try changing your queries to these:

SET @str = CONCAT('

CREATE VIEW tempView AS 

    SELECT * FROM (

        SELECT * FROM ', viewname, ' UNION SELECT * FROM ', viewname2, '

    )');



SET @str2 = CONCAT('DROP VIEW ', viewname);



SET @str3 = CONCAT('CREATE VIEW ', viewname, ' AS SELECT * FROM tempView');

Once you have done this you will no longer need any parameters to the EXECUTEs