concept dynamic sql in category sql

This is an excerpt from Manning's book SQL Server DMVs in Action: Better Queries with Dynamic Management Views. Login to get full access to this book.

In both of these cases, you could use a cursor to obtain the result, but cursor usage is typically discouraged because it can lead to inefficient scripts. But it’s possible to use another approach using dynamic SQL. This approach is used often in chapter 10.

Listing 2.9. Example of building dynamic SQL

The first part of listing 2.9 gets the details of the tables we want to obtain the row count from. This information is stored in a temporary table named #TableDetails . We then declare a dynamic variable of type NVARCHAR(MAX); this data type is needed for us to execute the dynamic SQL. The dynamic SQL is built up by concatenating the current value of the dynamic SQL with details of each row in the temporary table . Finally, the dynamic SQL is executed .