MySQL procedure with query string

Question

I am a newbie to using query strings in mysql and I have tried to write this procedure to drop tables under certain conditions. I don't reall know what I'm doing wrong and need help with getting the procedure to work or for someone to point me in the right direction. Thanks.

BEGIN

    DECLARE String scheduler = 'select status from mysql.scheduler where id=0' ;
    DECLARE String auftragpos = 'SELECT count("SchemaName") FROM "SYS.Tables" where "SchemaName" = dwh and "Name" = lexware_fk_auftragpos';
    DECLARE String auftrag = 'SELECT count("SchemaName") FROM "SYS.Tables" where "SchemaName" = dwh and "Name" = lexware_fk_auftrag';
    
    IF(auftragpos > 1)
    BEGIN
        drop table "dwh.lexware_fk_auftragpos";
    END
    
    IF(auftrag > 1)
    BEGIN
        drop table "dwh.lexware_fk_auftrag";
    END   
    
END

Answer 1

The syntax for declaring a variable is

DECLARE variablename datatype;

So it should be

DECLARE auftragpos INT;

Then you need to assign the result of the query to the variable. You do that by putting the SELECT query in parentheses.

You also should not quote table names, and you need to quote the literal strings you're using for the schema and table names you're searching for.

SET auftragpos = (
    SELECT count(*) 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'dwh' AND TABLE_NAME = 'lexware_fk_auftragpos');

But there's really no need to do this. If you want to prevent an error from DROP TABLE, just add the IF EXISTS option.

DROP TABLE IF EXISTS dwh.lexware_fk_auftragpos;