Dynamic SQL statements are the queries that are built at the run time of a program to form an executable SQL string. Even though static SQL is good and easy to build, sometimes data cannot be determined well in advance which leads to the usage of dynamic SQL. As any other programmable code, dynamic SQL also has few disadvantages such as security issues (Possible SQL Injection attacks), increasing code complexities. In this lab, we will cover how to write and execute dynamic SQL using various available approaches. In the first exercise, we will learn to write and execute dynamically build SQL string using EXEC command. In the second exercise, we will learn to execute dynamically build SQL string at linked servers. In the third exercise, we will learn to execute dynamically build SQL string using sp_executesql statement. Fourth exercise is a self-assessment where a problem will be given and you have to solve on your own.
Exercise 1: Dynamic SQL Using EXEC Command
Exercise 2: Dynamic SQL Using EXEC AT
Exercise 3: Dynamic SQL Using Sp_Executesql Statement
Exercise 4: Self-Assessment