Summary

Embedded SQL was the first commercial solution to extend SQL with the capabilities of a procedural language. It allows a host-language-written computer program to connect to RDBMS and perform virtually any SQL operation (retrieve and modify data, create, change, and drop database objects, grant and revoke privileges, etc.).

Dynamic SQL is a logical extension to the embedded (static) SQL that enables you to build and execute SQL statements in runtime.

Dynamic DML and DDL statements are relatively simple to implement because they do not have to return data back to the host program. Dynamic query standards are more complex and difficult to implement. Most modern programming languages delegate their implementation complexities to the internal mechanisms of the programming language — thus sparing the programmers.

Even though most sources say that dynamic SQL is generally less efficient than embedded SQL, that may or may not be true in different real-life situations. Understanding the general five steps of a SQL statement processing is the key to the appropriate use of embedded/dynamic SQL in each particular case.

Embedded/dynamic SQL is gradually becoming obsolete in the face of arising new database technologies, but it is far from being dead because of large amounts of legacy programming code. Embedded SQL has also found its place inside some modern technologies where it is the only choice — i.e., embedded databases.