T-SQL Tuesday is the brainchild of Adam Machanic (blog). Steve Jones (blog, Twitter) is currently maintaining this fantastic initiative.
This month’s topic has been chosen by Steve Jones (blog, Twitter), “Where have you used Dynamic SQL for in the past?“
My Contribution
In the past 10 years, I have used dynamic SQL several times in specific use cases. In this blog post, I will describe the use cases I can remember.
The first time I ran into dynamic SQL was when joining one of my previous employers. They built dashboards in a custom build internal application to look up customer information, current sales, visitors and more…
The application was retrieving information by executing a Stored Procedure on SQL Server. Since they wanted to view this information on different aggregation levels dynamically, dynamic SQL was used to change aggregation levels “easily”.
I can remember the hard time when we needed to make changes to the SQL code. The most common issue we were running into was too many or too less ‘-signs.
The second example I can remember is building ETL scripts using dynamic SQL. Since our ETL process was running based on Stored Procedures, we thought about a way to easily generate boilerplate SQL scripts where most of the code was already generated for us. To get this challenge solved, a couple of configuration tables were created (to configure the required column to take into count, which tables, and destination dimensions…) and then we used dynamic SQL to retrieve the code from our configuration tables and generate the major part of our ETL script. As a result, we could copy the script convert it into a Stored Procedure, and make some final adjustments and we were good to go.
The final example I can remember at this moment was to generate synonyms. For our ETL process, we were required to gather data from a certain system, which had a challenging naming convention in the table namings. Since we were making use of a Linked Server to retrieve the data, we generated synonyms to make the table names easier to read and most importantly, to make sure that we didn’t need to refer to the Linked Server in every script where we were querying these tables.
For me personally, there are 2 things to keep in mind when using dynamic SQL:
- You might get annoyed since having one ‘-sign too much or too less will make your code go wrong and it can be challenging to find out where you made a mistake.
- Getting dynamic SQL code readable is a huge challenge (for me at least)
Be First to Comment