What will I read: In this post we will look at TSQL Loops, what they can be used for and at a practical example. The below is applicable to MS SQL Server but I believe it also holds value for other databases.
An Intro… sort of
There are many opinions on loops. Some positive, many negative. Like it or not, they exist. The important thing is to know is how they work, and from there on out you can decern when is the right time to use them. One thing is always true is that water will choose the path of least resistance, and the same is true for developers.
The old joke goes: A C# Developer, a JavaScript Developer and a SQL Developer sat in a meeting room. The issue regarding a massive amount of logic came up and the C# dev went: Lets create a class to manage all this. The JavaScript developer argued no, we need a front end. The SQL Developer asked why we don’t handle it in a stored procedure and everyone but the SQL dev laughed. (I jest because I love)
Look, T-SQL, PLSQL and all the rest exists, the truth is, as long as you are able to craft a robust solution that works in your environment, you are gold. Let’s get to loops in T-SQL.
T-SQL Loops
Here is a simple loop:
DECLARE @Counter INT = 0
WHILE (@Counter <= 10)
BEGIN
PRINT (@Counter)
SET @Counter = @Counter +1
ENDThe above loop will basically print out the numbers 0 to 10 quickly. It functions on the same principles of any other coding language (now I am going to get laughed at because I am saying TSQL is coding)
Fine, but why would we ever need a loop in SQL. Well, the truth is, many reasons:
- If you are working on a database or data warehouse project where your only tool is SQL
- When you need to apply changes to Data objects
- If you need to build a script dynamically
- When you need to run a proc for multiple input parameters
- Example is if you need to reload or backdate data processing via the stored procedure.
An Example
Let’s look at a practical example I had to address the other day. The client has a database where they populate time sensitive data into multiple tables. Because of technical debt, they decided to put each day’s data into its own table (I guess partitioning didn’t exist when the project started?). So, they are left with something like the image to the right. Each day has its own table, and they have hundreds of thousands of Reckords in the tales. Their business users were viewing this data in a PowerBI dashboard that read the data from a view that had to be updated each day. The view was constructed out of multiple union all statements, kind of like the below:
SELECT [LocationID],[Amount]
FROM AdventureWorks2019.[dbo].[RandomDataTable20220101]
UNION ALL
SELECT [LocationID],[Amount]
FROM AdventureWorks2019.[dbo].[RandomDataTable20220102]
UNION ALL
SELECT [LocationID],[Amount]
FROM AdventureWorks2019.[dbo].[RandomDataTable20220103]
UNION ALL
SELECT [LocationID],[Amount]
FROM AdventureWorks2019.[dbo].[RandomDataTable20220104]
UNION ALL
SELECT [LocationID],[Amount]
FROM AdventureWorks2019.[dbo].[RandomDataTable20220105]Please don’t do this. This is not a great design. But it is what we have available so let’s try and improve it. In order to get rid of the requirement to manually update the view every day, we can drop the view every day as part of maintenance and recreate it dynamically in a Job with a loop. Please note this will not make the view faster, so performance on the view will be really bad (I will show you how to do this at the bottom of this post).
My recommendation was to perhaps create a stored procedure that will receive a date range (start and end dates). The procedure will then take those two dates and create the query based off the date range we need. They were happy with this idea as users had to enter a date range in the dashboard anyway.
So, the stored procedure does the following:
- Receives the start and end dates
- Loops through them and creates a dynamic SQL select script adding each required table to it
- Executes the dynamic SQL
Here is a script representative of the function of the stored procedure:
DECLARE @StartDate DATE = '2022-01-01'
, @EndDate Date = '2022-01-11'
, @CurrentDate Date
, @SQL_UNION VARCHAR(MAX)
SET @CurrentDate = @StartDate
WHILE @CurrentDate <= @EndDate
BEGIN
DECLARE @SQL_SELECT VARCHAR(MAX) = CONCAT('SELECT [LocationID], [Amount] FROM [AdventureWorks2019].[dbo].[RandomDataTable'
,FORMAT(@CurrentDate, 'yyyyMMdd')
,']')
SET @SQL_UNION = CONCAT(@SQL_UNION, CASE WHEN @SQL_UNION IS NOT NULL THEN CONCAT(CHAR(13),' UNION ALL ') END , @SQL_SELECT)
SET @CurrentDate = DATEADD(DAY,1,@CurrentDate)
END
PRINT @SQL_UNION
EXEC (@SQL_UNION)The above script will give you what you need. I replicated the issue in AdventureWorks, so if you want to try this at home you are welcome to. I also created those tables using a loop, here is the script if you are interested, the script for this is available in the Additional Scripts section later in this post.
A word of caution, always think through your task before you start developing it. And be cautious of implementing “temporary measures”. Those temporary measures might end up in production and you can incur some real technical debt because of it.
Hope this helped.
Additional Scripts
Creating and populating tables with a WHILE loop
The below code will create the tables and insert the data in the AdventureWorks DB. Please be cautious and use at your own discretion.
DECLARE @StartDate DATE = '2022-01-12'
, @EndDate Date = '2022-01-12'
, @Counter INT = 0
, @CurrentDate Date
SET @CurrentDate = @StartDate
WHILE @CurrentDate <= @EndDate
BEGIN
--CREATE TABLE
DECLARE @SQL_CREATE VARCHAR(MAX) = CONCAT( 'CREATE TABLE [AdventureWorks2019].[dbo].[RandomDataTable',FORMAT(@CurrentDate, 'yyyyMMdd'),'](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LocationID] [int] NULL,
[Amount] [float] NULL
)')
EXEC(@SQL_CREATE)
--INSERT DATA
DECLARE @SQL_INSERT VARCHAR(MAX) = CONCAT('
INSERT INTO [AdventureWorks2019].[dbo].[RandomDataTable'
,FORMAT(@CurrentDate, 'yyyyMMdd')
,']([LocationID]
,[Amount]) VALUES (FLOOR(RAND()*(10-5)+1),(RAND()*10))')
SET @Counter = 1
WHILE @Counter < 1000
BEGIN
EXEC (@SQL_INSERT)
SET @Counter = @Counter +1
END
SET @CurrentDate = DATEADD(DAY,1,@CurrentDate)
ENDCreating the VIEW with a WHILE Loop
The below code is to dynamically create the view, the option I did not like but wanted to include the code for your reference:
USE [AdventureWorks2019]
DECLARE @StartDate DATE = '2022-01-01'
, @EndDate Date = '2022-01-11'
, @CurrentDate Date
, @SQL_UNION VARCHAR(MAX)
SET @CurrentDate = @StartDate
WHILE @CurrentDate <= @EndDate
BEGIN
DECLARE @SQL_SELECT VARCHAR(MAX) = CONCAT('SELECT [LocationID], [Amount] FROM [AdventureWorks2019].[dbo].[RandomDataTable'
,FORMAT(@CurrentDate, 'yyyyMMdd')
,']')
SET @SQL_UNION = CONCAT(@SQL_UNION, CASE WHEN @SQL_UNION IS NOT NULL THEN CONCAT(CHAR(13),' UNION ALL ') END , @SQL_SELECT)
SET @CurrentDate = DATEADD(DAY,1,@CurrentDate)
END
IF OBJECT_ID(N'dbo.vwRandomDataRange', N'V') IS NOT NULL
BEGIN
DROP VIEW [dbo].[vwRandomDataRange]
END
SET @SQL_UNION = CONCAT('CREATE VIEW [dbo].[vwRandomDataRange] AS ', @SQL_UNION)
PRINT @SQL_UNION
EXEC (@SQL_UNION)PS. I know cursors exists and I did not use them because this post is about Loops, and frankly cursors are controversial.