There may be situation where you want to generate Netezza dynamic SQL queries inside your Netezza NZPLSQL procedures. Or, you may have procedures that generates other procedures or CREATE TABLE or update the records form the table based on some condition. You can also set some session specific variables dynamically inside the stored procedure and execute the queries.
For such situations, Netezza NZPLSQL provides the “EXECUTE IMMEDIATE” statement. You can execute the Netezza dynamic SQL queries only in stored procedures. You cannot execute the queries within Netezza block statements.
Read:
Syntax:
EXECUTE IMMEDIATE query-string ;
The query-string value is a text string which holds the query to be executed. Note that, when working with dynamic queries, make sure that you escape any single quotes in Netezza NZPLSQL otherwise you will end up getting errors during execution. During developing any Netezza dynamic SQL queries, you can display that query and see if it is perfect.
Display String values using NZPLSQL Raise Notice
For instance, you can write something like below to display string values in SP.
RAISE NOTICE 'Query string: %', query-string;
Netezza Dynamic SQL Example:
EXECUTE IMMEDIATE 'UPDATE table SET' || quote_ident('old value') || ' = ' || quote_literal('updated value') || ' WHERE ...';
This example demonstrates the use of the quote_ident and quote_literal functions. To ensure that strings are correctly processed for quotes or special characters, expressions containing column and table identifiers should be passed to quote_ident
A query run by an EXECUTE IMMEDIATE statement is always prepared dynamically each time the statement is run. You can create the Netezza dynamic SQL inside Netezza stored procedure that can perform the particular actions.
on different tables and fields based on the different situations and conditions.
Limitations of EXECUTE IMMEDIATE statements
Below are couple of limitations:
- EXECUTE IMMEDIATE will discard any SELECT queries result. It will not return any records. You need to use REFTABLE in case you want to return results set.
- EXECUTE IMMEDIATE does not supports SELECT INTO
- You can iterates throgh the records to perform the spcific task on the records