example.sql

-- 1
SET @table = "pexeso";
SET @sql:= CONCAT('SELECT * FROM',' ', @table);
PREPARE stmt FROM @sql;
EXECUTE stmt;
-- DEALLOCATE PREPARE stmt;  # neni povinne


-- 1b, lze dosadit parametry pres ?
SET @id = 1;
PREPARE stmt FROM 'SELECT * FROM category WHERE id = ?';
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt; 




-- 2, Dynamically Building SQL based on Parameters
CREATE DEFINER=`root`@`localhost` 
  PROCEDURE `GetRecentActivity`(input VARCHAR(15))
BEGIN
SET @input = input;

if @input="asc" then
    SET @sort = " order by activity_log_key asc";
elseif @input = "desc" then
    SET @sort = " order by activity_log_key desc";
else
    SET @sort ="";
end if;

SET @query = CONCAT('select * from activitylog ',@sort,' limit 0, 5');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END


-- 3, more than one column
SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.`COLUMNS` C 
WHERE table_name = 'MyTb' AND COLUMN_NAME LIKE '%whatever%' INTO @COLUMNS;

SET @table = 'MyTb';
SET @s = CONCAT('SELECT ',@columns,' FROM ', @table);

PREPARE stmt FROM @s;
EXECUTE stmt;