How to execute a dynamic query inside a stored procedure

Hello, i need help to create a stored procedure that executes a dynamic query.

Sql server example:

DECLARE @ConsultaSQL NVARCHAR(500)
DECLARE @Tabla NVARCHAR(25)
DECLARE @Edad1 INT
DECLARE @Edad2 INT

SET @Edad1 = 50
SET @Edad2 = 70

SET @Tabla = ‘Empleados’
SET @ConsultaSQL = ‘SELECT * FROM ’ + @Tabla + ’ WHERE Edad BETWEEN @Edad1 AND @Edad2

–Ejecución de consulta dinámica
EXEC SP_EXECUTESQL @ConsultaSQL, N’@Edad1 INT, @Edad2 INT’, @Edad1, @Edad2

Hello,

You can find complete examples of dynamic SQL in MemSQL here: .

I rewrote your code below. Please take note of the SQL_MODE variable being set. That is required when using pipes to concatenate your strings. Also, MemSQL does not allow SELECT statements in a procedure without ECHO. You will see that in the code below. You can read up on ECHO SELECT here

SET sql_mode = 'PIPES_AS_CONCAT';
DELIMITER  //
CREATE OR REPLACE PROCEDURE foo() AS 

DECLARE
ConsultaSQL VARCHAR(500);
Tabla VARCHAR(25) = 'Empleados';
Edad1 INT = 50;
Edad2 INT = 70;

BEGIN

ConsultaSQL = 'ECHO SELECT * FROM ' || Tabla || ' WHERE Edad BETWEEN ' || Edad1 || ' AND ' || Edad2;

EXECUTE IMMEDIATE ConsultaSQL;
END;
//
DELIMITER  ;
2 Likes