“Dynamic SQL is the SQL statement that is constructed and executed at runtime based on input parameters passed”<\/p>\n\n\n\n
https://www.sqlshack.com\/dynamic-sql-in-sql-server\/<\/code><\/pre>\n\n\n\nExample: ChatGPT<\/p>\n\n\n\n
DECLARE @sql AS NVARCHAR(MAX) <\/p>\n\n\n\n
SET @sql = ‘SELECT * FROM Employees WHERE Department = ”’ + @department + ””<\/p>\n\n\n\n
EXEC sp_executesql @sql<\/p>\n\n\n\n
<\/p>\n\n\n\n
Another Example: Oracle<\/p>\n\n\n\n
table_name := ‘HR.Employees’;<\/p>\n\n\n\n
dyn_sql := ‘Select * from ‘ || table_name || ‘ FETCH FIRST 10 ROWS ONLY ‘;<\/p>\n\n\n\n
EXECUTE IMMEDIATE dyn_sql;<\/p>\n\n\n\n
DECLARE\n table_name VARCHAR2(100):= '';\n dyn_sql VARCHAR2(100):= '';\nBEGIN \n table_name := 'HR.Employees';\n dyn_sql := 'Select * from ' || table_name || ' FETCH FIRST 10 ROWS ONLY ';\n EXECUTE IMMEDIATE dyn_sql;\nEND;<\/code><\/pre>\n\n\n\nA simple dynamic SQL in a stored Procedure (Oracle)<\/p>\n\n\n\n
CREATE OR REPLACE PROCEDURE Retrieve_10_ROWS\nIS\n dyn_sql VARCHAR2(200);\n table_name VARCHAR2(50);\nBEGIN \n table_name := 'HR.Employees';\n dyn_sql := 'Select * from ' || table_name || ' FETCH FIRST 10 ROWS ONLY ';\n \n EXECUTE IMMEDIATE dyn_sql;\nEND;\n\nEXEC Retrieve_10_ROWS;\n<\/code><\/pre>\n\n\n\nDynamic\/Reference Cursor and Dynamic SQL<\/p>\n\n\n\n
CREATE OR REPLACE PROCEDURE Retrieve_10_ROWS_dyn_cur(p_table_to_query VARCHAR2) IS\n dyn_sql VARCHAR2(200);\n JOB_ID VARCHAR2(50);\n JOB_TITLE VARCHAR2(100); \n TYPE cur_typ IS REF CURSOR;\n c cur_typ;<\/strong>\nBEGIN\ndyn_sql := 'Select JOB_ID, JOB_TITLE from ' || p_table_to_query || ' FETCH FIRST 10 ROWS ONLY ';\n OPEN c FOR dyn_sql; <\/strong>\n --USING table_name;\n LOOP\n FETCH c INTO JOB_ID, JOB_TITLE; <\/strong> \n EXIT WHEN c%NOTFOUND;\n -- process row here\n DBMS_OUTPUT.PUT_LINE(JOB_ID || ' ' || JOB_TITLE);\n END LOOP;\n CLOSE c;\n EXECUTE IMMEDIATE dyn_sql;\nEND;\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"“Dynamic SQL is the SQL statement that is constructed and executed at runtime based on input parameters passed” 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 ‘ || … <\/p>\n