Dynamic SQL

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:

  1. When we need to run dynamic queries on our database, mainly DML queries.
  2. When we need to access an object which is not in existence during the compile time.
  3. Whenever we need to optimize the run time of our queries.
  4. When we need to instantiate the created logic blocks.
  5. 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.

Syntax:

-- 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;

Example:

Let us now see a Dynamic SQL in working condition by defining the same on our IBM database.

Query:

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;


Copyright 1999- Ducat Creative, All rights reserved.

Anda bisa mendapatkan server slot online resmi dan terpercaya tentu saja di sini. Sebagai salah satu provider yang menyediakan banyak pilihan permainan.