Dynamic SQL – Build SQL during run time

About Dynamic SQL

Dynamic SQL programming technique enables you to build SQL dynamically during run time. Many flexible applications can be created using Dynamic SQL because the complete information about the SQL statement may be unknown during the compilation.

Dynamic SQL is used in case where static SQL support the operation or you do not know the exact SQL statements that should be executed. These statements may depend upon the user input or the processing work done by the program.

Different SQL statements can be executed for each input row using Dynamic SQL.

Dynamic SQL Applications

Below are some the types of applications where dynamic SQL needs to be used,

• End user chooses the query or input at run-time.
• The end user selects Input or optimizer at run-time.
• Queries a database where the data definitions of tables change constantly.
• Queries a database where new tables created frequently.

Dynamic SQL – Example

Let’s say we want to implement “Patients Search” web page as shown below

As per the search fields given by the end user, we will be searching the corresponding strings in the patients table.

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

Patients info table which is already available in database is as below.

Now we will search the Patient’s info as below

In the below example, Let us assume that the user has provided values only for First_Name and Last_Name search fields. To execute dynamic SQL we are using system stored procedure sp_executesql.

Usually sp_executesql takes 2 predefined parameters and n – number of user defined parameters.

@statement – This is the first parameter which is mandatory and contains the SQL statements to execute.

@params – This is another parameter which is optional. This is to declare parameters that are specified in @statement.

Other parameters are the parameters that you had declared in the @params.

Here if user enters only the First_Name = Tim and Last_Name = Stuart in the search form, this will fetch all the details about the Patient from the database

Dynamic SQL – Advantages

• Speeds up complex queries
• Increases the re-usability of the same query.
• Generates large or complex SQL and automatically.
• In the face of changing requirements, it has a very rapid response and flexibility.
• Provides the ability to execute SQL statements where the structure is unknown until the run-time.

Glossary

• SQL – Structured Query Language
• SP – Stored procedure