Dynamic SQL-Dynamically Generating Queries in SQL Server

Handling large volumes of data can be tricky. Every day as a data analyst and MIS executive, you have to write so many queries. Each time a new requirement comes up, who has to design and execute a new query? What if the same query can be used for multiple purposes? This is what dynamic SQL does. Over the following few paragraphs, we will discuss dynamic SQL in detail.

What is Dynamic SQL Query?

Dynamic SQL is a programming technique enabling you to build SQL statements at runtime dynamically. You can create more general-purpose, flexible applications utilizing dynamic SQL, as the full text of a SQL statement may not be known at compilation. 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 manage big industrial applications and transactions without any added overhead. With dynamic SQL, we are free to create flexible SQL queries, and the variables' names or any other parameters are passed when the application runs. We can use dynamic sql in stored procedure to create dynamic queries that can run when desired.

For Dynamic SQL, we use the exec keyword. When we use static SQL it is not altered from one execution to another, 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 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.

What are The Needs for 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 that does not exist 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.

Requirements of Dynamic SQL

To showcase a dynamic SQL statement, a character string must have the text of a valid SQL statement but not include the EXEC SQL clause, host-language delimiters or statement terminator, or any of the following embedded SQL commands:

• CLOSE
• DECLARE
• DESCRIBE
• EXECUTE
• FETCH
• INCLUDE
• OPEN
• PREPARE
• WHENEVER

Almost in all cases, the character string can have dummy host variables. They have a place in the SQL statement for actual host variables. Because dummy host variables are just placeholders, you cannot declare them and name them anything you like. For example, SQL Server makes no difference between the following two strings:

DELETE FROM EMP WHERE MGR = :mgr_number AND JOB = :job_title
DELETE FROM EMP WHERE MGR = :m AND JOB = :j

How Dynamic SQL Statements are Processed?

Generally, an application program alerts the user for the text of a SQL statement and the values of host variables used in the statement. Then SQL Server parses the SQL statement. SQL Server checks the SQL statement to make sure it adheres to syntax rules and refers to valid database objects. Parsing also includes checking database access rights, reserving needed resources, and finding the optimal access path.

Next, SQL Server binds the host variables to the SQL statement. That is, SQL Server gets the addresses of the host variables so that it can read or write their values.Then SQL Server executes the SQL statement. SQL Server does what the SQL statement requested, such as deleting rows from a table.The SQL statement can be executed continuously using new values for the host variables.

Methods of Dynamic SQL

This section presents four methods to define dynamic SQL statements. It describes the capabilities and limitations of each method and then offers guidelines for choosing the correct method. Later sections show you how to use the methods. Also, you can find sample host-language programs in your supplement to this Guide.

The four methods are increasingly general. Method 2 encloses Method 1, Method 3 encloses Methods 1 and 2, and so on. However, each method is most helpful in handling a certain kind of SQL statement, as shown below.

The term select-list item contains column names and expressions.

Method 1

This process lets your program accept or create a dynamic SQL statement, then right away enforce it utilizing the EXECUTE IMMEDIATE command. And one should never forget that the SQL statement must not be a query (SELECT statement) and must not have any placeholders for input host variables. For example, the following host strings will qualify:

DELETE FROM EMP WHERE DEPTNO = 20
GRANT SELECT ON EMP TO Scott

With Method 1, the SQL statement is parsed all time it is executed (unless you specify HOLD_CURSOR=YES).

Method 2

This method lets your program accept or assemble a dynamic SQL statement, then process it utilizing the PREPARE and EXECUTE commands. Make sure the SQL statement is not a query. The number of placeholders for input host variables and the datatypes of the input host variables must be known at precompile time. For example, the following host strings fall into this category:

INSERT INTO EMP (ENAME, JOB) VALUES (:emp_name, :job_title)
DELETE FROM EMP WHERE EMPNO =:emp_number

With Method 2, the SQL statement is parsed just once (unless you mention RELEASE_CURSOR=YES), but it can be executed multiple times with various values for the host variables. SQL data definition statements such as CREATE are implemented when they are PREPAREd.

Method 3

This method lets your program take or build a dynamic query, then operate it using the PREPARE command with the DECLARE, OPEN, FETCH, and CLOSE cursor commands. The number of select-list items, the number of placeholders for input host variables, and the datatypes of the input host variables must be known at precompile time. For example, the following host strings will qualify:

SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP GROUP BY DEPTNO
SELECT ENAME, EMPNO FROM EMP WHERE DEPTNO =:dept_number

Method 4

This method lets the program accept or build a dynamic SQL statement, then operate it using descriptors. The number of select-list items, the number of placeholders for input host variables, and the datatypes of the input host variables can be known once run time. For example, the following host strings fall into this category:

INSERT INTO EMP () VALUES ()
SELECT  FROM EMP WHERE DEPTNO = 20

Method 4 is needed for dynamic SQL statements that have an unknown number of select-list items or input host variables.

How to Use Dynamic SQL?

Following SQL Statement shows how to use dynamic SQL Statement.

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @name varchar(75)
SET @columnList = 'Empid, Empname, Address'
SET @name = '''Mohan'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM [Employee] WHERE Empname = ' + @name
EXEC (@sqlCommand)

Now you know how to use sql server dynamic sql and sql server execute dynamic sql.

Advantages of Dynamic SQL

Host programs that accept and implement dynamically defined SQL statements are more flexible than plain embedded SQL programs. Dynamic SQL statements can be created with inputs from users with little or no SQL knowledge.

For example, your program may alert users for a search condition to be utilized in the WHERE clause of a SELECT, UPDATE, or DELETE statement. A more difficult program might allow users to choose from menus listing SQL operations, table and view names, column names, etc. Therefore, dynamic SQL lets you create highly adaptable applications.However, some dynamic queries require complex coding, unique data structures, and more runtime processing. While you might overlook the added processing time, the coding might be complicated unless you fully understand dynamic SQL concepts and methods.

 Disadvantages of Dynamic SQL

The following are the disadvantages of Dynamic SQL.

• Performance loss: the execution plan for a dynamic query cannot be cached.
• Hard to debug.
• The error management becomes more unreliable.
• Temporary tables from the main statement cannot be used unless they are global.

Difference Between Static and Dynamic SQL

SQL Training For Administrators & Developers

• No cost for a Demo Class
• Industry Expert as your Trainer
• Available as per your schedule
• Customer Support Available

Enroll for Demo Class

Conclusion

Over the last few paragraphs, we have discussed the different features of Dynamic SQL. We have also learned about its uses, merits, and demerits. Hope this write-up gives the reader a good understanding of the topic and encourages them to study further on the subject.