SQL & My SQL Tutorial
- What is SQL?
- SQL Microsoft
- SQL Management Tools
- SQL Developer
- Docker Commands
- Composite Key in SQL
- SQL Constraints
- Transactions in SQL Server
- SQL Server Data Types
- SQL Update Join
- SQL Operators
- SQL Clauses
- SQL Commands
- SQL Alter Command
- Distinct Keyword in SQL
- SQL Statements
- SQL Index
- SQL Injections
- Wildcards in SQL
- Alter and Truncate Command in SQL
- SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF
- SQL Sequence
- How to Find Duplicate Records in SQL
- Primary and Foreign Key in SQL with Examples
- COUNT Function
- SUM Function in SQL
- Dynamic SQL
- Database Tuning
- Pseudocolumn in Oracle SQL
- Triggers in SQL
- Embedded SQL in DBMS
What is Dynamic SQL?
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.
It helps us to manage big industrial applications and manage the transactions without any added overhead.
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.
We can use stored procedures to create dynamic queries which can run when we desire.
For Dynamic SQL, we use the exec keyword.
When we use static SQL it is not altered from one execution to others, but in the case of dynamic SQL, we can alter the query in each execution.
We should always prefer using static SQL over dynamic SQL for the following benefits of the static SQL:
- If a query compiles successfully it implies that the syntax is correct.
- If a query compiles successfully it verifies that all the permissions and validations are correct.
- As all the data is pre-known in static SQL the overhead charges are reduced considerably.
Why do we need Dynamic SQL?
We need to use Dynamic SQL for the following use cases:
- When we need to run dynamic queries on our database, mainly DML queries.
- When we need to access an object which is not in existence during the compile time.
- Whenever we need to optimize the run time of our queries.
- When we need to instantiate the created logic blocks.
- When we need to perform operations on application fed data using invoker rights.
How to use Dynamic SQL?
We need to follow the following syntax while creating and executing a dynamic SQL cycle.
-- 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;
Let us now see a Dynamic SQL in working condition by defining the same on our IBM database.
Use IBM; -- Set the value of user-defined variables SET @id = 'A01'; -- set the query you want to execute on the database SET @query = 'SELECT * FROM IBM where emp_id = @id'; -- Prepare the statement to be run on the database PREPARE stmt FROM @query; -- Execute the prepared statement Execute stmt;