Avoiding dynamic SQL

Performance of static SQL is generally better than dynamic SQL as we all know. Here are a few simple ways to avoid writing dynamic SQL in circumstances where dynamic SQL can seem inevitable

This first one is a simple condition where based on the value of a parameter, the SQL needs to use either the parameter or a constant. Hence the statement is modified within the condition appropriately.

IF (alt_id > 1) THEN
serialstmt := serialstmt || ‘SII.alt_id = ‘ || alt_id || ‘ ‘;
ELSE
serialstmt := serialstmt || ‘SII.alt_id = 1 ‘;
END IF;
What you see below is a static SQL implementation of the same.
SII.alt_id IN(
SELECT as_alt_id
FROM DUAL
WHERE as_alt_id > 1
UNION
SELECT 1
FROM DUAL
WHERE as_alt_id <= 1) AND

The next one, a more complex example, involves a conditional string comparison - another common scenario.

IF (job_nbr IS NOT NULL AND job_nbr != “) THEN
serialstmt := serialstmt || ‘AND SSEWO.job_nbr = “” || job_nbr || “” ‘;
END IF;
Can be written as
SSEWO.job_nbr IN(
SELECT as_job_nbr
FROM DUAL
WHERE as_job_nbr IS NOT NULL
UNION
SELECT DISTINCT substep_ewo.job_nbr
FROM DUAL,
substep_ewo
WHERE as_job_nbr IS NULL)

The code should be self explanatory. The second one simply retrieves all distinct job numbers when the parameter is null, which is the same as not having a condition.
A technique that can prove costly of economical depending on the nature of the data the SQL operates on.

In both cases, conditions that required SQL statement to be built dynamically were incorporated in static SQL statements.

More information about dynamic SQL itself can be found at Coding Dynamic SQL Statements