Register now or log in to join your professional community.
Static SQL is SQL statements in an application that do not change at runtime.
Dynamic SQL is an enhanced form of Structured Query Language (SQL) that, unlike standard (or static) SQL, facilitates the automatic generation and execution of program statements
Static SQL is SQL statements in an application that do not change at runtime and, therefore, can be hard-coded into the application. Dynamic SQL is SQL statements that are constructed at runtime; for example, the application may allow users to enter their own queries. so, the SQL statements cannot be hard-coded into the application.
When you use static SQL, you cannot change the form of SQL statements unless you make changes to the program. However, you can increase the flexibility of static statements by using host variables.
Example: In the following example, the UPDATE statement can update the salary of any employee. At bind time, you know that salaries must be updated, but you do not know until run time whose salaries should be updated, and by how much. 01 IOAREA. 02 EMPID PIC X(06). 02 NEW-SALARY PIC S9(7)V9(2) COMP-3. ⋮ (Other declarations) READ CARDIN RECORD INTO IOAREA AT END MOVE 'N' TO INPUT-SWITCH. ⋮ (Other COBOL statements) EXEC SQL UPDATE DSN8B10.EMP SET SALARY = :NEW-SALARY WHERE EMPNO = :EMPID END-EXEC. The statement (UPDATE) does not change, nor does its basic structure, but the input can change the results of the UPDATE statement. Flexibility of dynamic SQLWhat if a program must use different types and structures of SQL statements? If there are so many types and structures that it cannot contain a model of each one, your program might need dynamic SQL.
You can use one of the following programs to execute dynamic SQL: Db2 Query Management Facility™ (QMF™) Provides an alternative interface to Db2 that accepts almost any SQL statement
Static is hard codded and not changealbe during execution and it is less flexible but the dynamic query is complied run time and more flexible
static sql query is not changed during execution time is hardcodeddynamic sql query : the query structure can be changed during execution time either by end user or other object
Structured Query Language and facilitates the automatic generation
Static vs Dynamic SQL
Static SQL
its parsed, validated and optimized once before execution.
Main advantage that runtime overhead is removed.
Main Disadvantage is that the query, which was parsed, validated and optimized once may not remain when in run later.
Dynamic SQL
Its parsed, validated and optimized at runtime as its called.
Main Pro is that all required info is up to dated.
Main Cons that each time the query is to be parsed, validated and optimized before execution.
Static SQL are SQL statements in an application that do not change at runtime and, therefore, can be hard-coded into the application. Dynamic SQL is SQL statements that are constructed at runtime; for example, the application may allow users to enter their own queries.
Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.
Static SQL is fixed at runtime while Dynamic SQL gives the flexibility to customise according to needs at run time and it is not hard coded.
Dynamic query allows the end-uers or a procedure to load query parameters at runtime. Static is hardcoded.
Static SQL statements in an applications do not change at runtime so they are hard coded in application.
Dynamic SQL statements in an applications can be embeded at run time i.e users can enter their own queries.
