Dynamic SQL vs Embedded SQL

 SQL (Structured Query Language) – It is used to retrieve and edit data or information stored in a database. In 1986, the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) both recognised SQL as a standard.

 

DYNAMIC SQL?

SQL statements that are generated or executed dynamically are known as run-time SQL statements. Users can run their queries in some apps by using dynamic SQL. At runtime, these statements are compiled. When the data recorded in our database is not uniform, these kinds of SQL queries are employed. Compared to embed SQL, it is more adaptable and can be employed in some dynamic applications.

WHEN TO USE DYNAMIC SQL?

When static SQL cannot accomplish the task you want it to or when you are unsure of the precise SQL lines that a PL/SQL process must run, you should utilize dynamic SQL. The following sections outline common scenarios in which dynamic SQL should be used and typical issues that dynamic SQL may help with.

1.     To Execute Dynamic DML Statements

2.     To Execute Statements Not Supported by Static SQL in PL/SQL

3.     To Execute Dynamic Queries.

4.     To Reference Database Objects that Do Not Exist at Compilation

 

Steps to use Dynamic SQL

1.     Declare two variables, @var1 for holding the name of the table and @var 2 for holding the dynamic SQL :

DECLARE

@var1 NVARCHAR(MAX),

@var2 NVARCHAR(MAX);

 

2.     Set the value of the @var1 variable to table_name :

SET @var1 = N'table_name';

 

3.     Create the dynamic SQL by adding the SELECT statement to the table name parameter :

SET @var2= N'SELECT *

FROM ' + @var1;

 

4.     Run the sp_executesql stored procedure by using the @var2 parameter :

EXEC sp_executesql @var2;

 

Dynamic SQL queries

For each row of input, a dynamic SQL query may run a distinct query statement. String substitution is used to define string variables in a dynamic SQL query and connect them to input fields in the transformation.

Create a string variable in the query for the part of the query you wish to update in order to alter the query statement. Specify the name of an input field in the query and wrap it in tilde characters () to configure the string variable. Depending on the value of the data in the field, the query varies.

The query variable's input field for the transformation must be of the string data type. To alter the query data and the query statement, utilize string substitution.

Data Integration creates a query for each input row when you construct a dynamic SQL query. The following dynamic query types are passable in an input field:

1.     Full query – You can use the query statements from the source data to replace the whole SQL query.

2.     Partial query – These queries allow you to change just a section of the query, such as the table name.

 

Benefits of Dynamic SQL

1.     Predicate Optimization – In dynamic SQL, executing plans are prepared for every query execution, but only those executions that are being used are optimized.

2.    Single Query Plan Caching – There will be one cached query plan for each stored procedure as well as an extra ad hoc plan cache for each time the stored procedure is invoked.

Drawbacks of Dynamic SQL

1.     Speed – Because SQL Server must create an execution plan each time during runtime, dynamic SQL is slower than static SQL.

2.     Permissions — Dynamic SQL mandates that all accessible objects, including tables and views, have direct access permissions for all users.

3.      Syntax – Writing stored T-SQL procedures has another significant benefit in that you may instantly receive a syntax check.

EMBEDDED SQLs   

It is a technique for fusing a programming language's processing capability with SQL's database management skills. The host language's source code is followed while writing embedded SQL statements. This makes it possible for programmers to include SQL statements in code written in a variety of languages, including C/C++, COBOL, and FORTRAN.

Inline SQL statements or queries can be inserted using the embedded SQL technique into the code of a host language, which is a computer language. A reliable and practical way to combine the processing power of a programming language with SQL's specific data management and manipulation capabilities is through embedded SQL.

Why do we need Embedded SQL?

1.     We have the flexibility to use databases as needed thanks to embedded SQL. Several tasks must be completed before the application we design enters production mode.

2.     The issue of authorization and the retrieval and feeding of data into and out of the database is one of a thousand things we need to take care of.

3.    We can quickly utilize the database without writing any cumbersome code thanks to the embedding of queries. We can build APIs that quickly retrieve and feed data as needed thanks to the inbuilt SQL.

How to Embed SQL in High-Level Languages?

1.    Each high-level language needs a few tools to use embedded SQL. We sometimes have built-in libraries that provide us with the fundamental building blocks.

2.    While in certain circumstances we must import or use specific packages to carry out the required operations.

3.    For instance, a connection class is required in Java. First, we use the connection class to build a connection, and then we open the connection without providing the necessary parameters to connect to the database.

Embedded SQL Statements

BEGIN DECLARE SECTION	FETCH	SET AUTOCOMMIT
BEGIN TRAN	GET CONNECTION	SET CONCURRENCY
BIND CONNECTION	GET HDBC	SET CONNECTION
CALL	GET HENV	SET host_variable
CLOSE	GET NEXT RESULT SET	SET OPTION
COMMIT	INCLUDE	SET SCROLLOPTION
CONNECT	INSERT	SET TRACELEVEL
DECLARE CURSOR	INTO	SET TRANSACTION ISOLATION
DECLARE DATABASE	OPEN	SYNCPOINT
DECLARE STATEMENT	PERSIST	UNBIND CONNECTION
DELETE(Positioned)	PREPARE	UPDATE(Positioned)
DELETE(Searched)	QUERY ODBC	UPDATE(Searched)
DESCRIBE	RESET CONNECTION	Whenever
DISCONNECT	ROLLBACK	EXECUTE

 

Systems that support embedded SQL

Advantages of Embedded SQL

Following are a few benefits of using Embedded SQL in high-level languages:

a)     Facilitates remote database access.

b)     Makes it possible to integrate authentication services for extensive applications.

c)     Increases database transactions' security.

d)     When carrying out transactions on our database, avoids logical mistakes.

e)     Makes it simple to combine our application's front and back ends.

 

Difference between Embedded SQL and Dynamic SQL

Key	Static/Embedded SQL	Dynamic SQL
Database Access	The database access method in Static SQL is predetermined in the statement.	Only at runtime may Dynamic SQL be used to determine how a database will be accessed.
Efficiency	SQL statements that are static are quicker and more effective.	SQL statements that are dynamic are less effective.
Compilation	At compilation time, static SQL statements are created.	Run-time compilation occurs for dynamic SQL statements.
Application Plan	Compile-time operations include Application Plan parsing, validation, optimization, and creation.	Run-time operations include application plan parsing, validation, optimization, and creation.
Use Cases	Used when the data is evenly distributed.	Used when the data is not spread equally.
Dynamic Statements	It is not necessary to utilise commands like EXECUTE IMMEDIATE, EXECUTE, and PREPARE.	There are phrases like "EXECUTE IMMEDIATELY," "EXECUTE," and "PREPARE."
Flexibility	Less Adaptable.	Extremely Adaptable

 

Conclusion –

In this Blog, We came to know about Embedded and Dynamic SQL, how to utilize them, and also about their Advantages, disadvantages Limitations, etc.

Due to the widespread support Embedded SQL receives from nearly all developers, SQL can be incorporated into virtually all high-level languages. SQL integration is supported by languages like C, C++, Java, etc. For incorporating database queries into the programming, certain languages, like Python, include built-in libraries. We have the SQLite package for Python, which makes the embedding process for connecting to the database simple.

Dynamic SQL may be used to create adaptable applications, but only if your users are knowledgeable experts. Otherwise, you ought to use embedded or static SQL. Compared to dynamic SQL, this is more effective.