“Dynamic SQL is the SQL statement that is constructed and executed at runtime based on input parameters passed”
https://www.sqlshack.com/dynamic-sql-in-sql-server/Example: ChatGPT
DECLARE @sql AS NVARCHAR(MAX)
SET @sql = ‘SELECT * FROM Employees WHERE Department = ”’ + @department + ””
EXEC sp_executesql @sql
Another Example: Oracle
table_name := ‘HR.Employees’;
dyn_sql := ‘Select * from ‘ || table_name || ‘ FETCH FIRST 10 ROWS ONLY ‘;
EXECUTE IMMEDIATE dyn_sql;
DECLARE
table_name VARCHAR2(100):= '';
dyn_sql VARCHAR2(100):= '';
BEGIN
table_name := 'HR.Employees';
dyn_sql := 'Select * from ' || table_name || ' FETCH FIRST 10 ROWS ONLY ';
EXECUTE IMMEDIATE dyn_sql;
END;A simple dynamic SQL in a stored Procedure (Oracle)
CREATE OR REPLACE PROCEDURE Retrieve_10_ROWS
IS
dyn_sql VARCHAR2(200);
table_name VARCHAR2(50);
BEGIN
table_name := 'HR.Employees';
dyn_sql := 'Select * from ' || table_name || ' FETCH FIRST 10 ROWS ONLY ';
EXECUTE IMMEDIATE dyn_sql;
END;
EXEC Retrieve_10_ROWS;
Dynamic/Reference Cursor and Dynamic SQL
CREATE OR REPLACE PROCEDURE Retrieve_10_ROWS_dyn_cur(p_table_to_query VARCHAR2) IS
dyn_sql VARCHAR2(200);
JOB_ID VARCHAR2(50);
JOB_TITLE VARCHAR2(100);
TYPE cur_typ IS REF CURSOR;
c cur_typ;
BEGIN
dyn_sql := 'Select JOB_ID, JOB_TITLE from ' || p_table_to_query || ' FETCH FIRST 10 ROWS ONLY ';
OPEN c FOR dyn_sql;
--USING table_name;
LOOP
FETCH c INTO JOB_ID, JOB_TITLE;
EXIT WHEN c%NOTFOUND;
-- process row here
DBMS_OUTPUT.PUT_LINE(JOB_ID || ' ' || JOB_TITLE);
END LOOP;
CLOSE c;
EXECUTE IMMEDIATE dyn_sql;
END;
