DBMS: Dynamic SQL
Akhilesh Arya
Static VS Dynamic SQL
• Static SQL refers to those SQL statements which
are fixed and can be hard coded into the
application.
• Dynamic SQL refers to those SQL statements
which are generated dynamically based on
user's input and run in the application
• Dynamic Sqls helps to develop general and
flexible applications
Cont..
• Dynamic SQL is the process that we follow for
programming SQL queries in such a way that the
queries are built dynamically with the application
operations.
• With dynamic SQL we are free to create flexible SQL
queries and the names of the variables or any other
parameters are passed when the application runs.
Cont..
• It helps us to manage big industrial
applications and manage the transactions
without any added overhead.
• For Dynamic SQL, we use the exec or
execute keyword.
# Start by declaring the Query variable and other required variables
DECLARE @SQL nvarchar(1000)
DECLARE @variable1 varchar(50)
DECLARE @variable2 varchar(50)
# Set the values of the declared variables if required
SET @variable1 = 'A'
# Define the query variable
SET @SQL = 'SELECT columnName1, columnName2, columnName3...
FROM tableName where columnName1 = @variable1
# Prepare the statement to be run on the database
PREPARE Query FROM @SQL;
# Execute the prepared Dynamic SQL statement
Execute Query;
Use TestDatabase; #Database
# Create Variables
Declare @Table varchar(100);
Declare @ColList varchar(100);
Declare @Query varchar(100);
# set the vales of the variables
Set @Table =‘empHr’;
Set @ColList=‘empID, empName, salary, joinYear’;
Set @Query=CONCAT(‘select’, @ColList, ‘from’, @Table)
EXEC(@Query)
Sr. No. Key Static SQL Dynamic SQL
Database In Static SQL, database access In Dynamic SQL, how a database
1 Access procedure is predetermined in the will be accessed, can be determine
statement. only at run time.
Efficiency Static SQL statements are more Dynamic SQL statements are less
2 faster and efficient. efficient.
Compilation Static SQL statements are compiled Dynamic SQL statements are
3 at compile time. compiled at run time.
Application Application Plan parsing, Application Plan parsing,
Plan validation, optimization and validation, optimization and
4 generation are compile time generation are run time activities.
activities.
Dynamic Statements like EXECUTE Statements like EXECUTE
5 Statements IMMEDIATE, EXECUTE, PREPARE IMMEDIATE, EXECUTE, PREPARE
are not used. are used
6 Flexibility Static SQL is less flexible. Dynamic SQL is highly flexible.
Active Database
• An active Database is a database consisting of a set
of triggers.
• These databases are very difficult to be
maintained because of the complexity that arises
in understanding the effect of these triggers.
• In such a database, DBMS initially verifies
whether the particular trigger specified in the
statement that modifies the database) is activated
or not, prior to executing the statement.
• If the trigger is active then
DBMS executes the
condition part and then
executes the action part
only if the specified
condition is evaluated to
true.
Advantages of Active database
• It Enhances traditional database functionalities
with powerful rule processing capabilities.
• Enable a uniform and centralized description of
the business rules relevant to the information
system.
• Avoids redundancy of checking and repair
operations.
• A suitable platform for building a large and
efficient knowledge base and expert systems.
