Simple While Loop Example - Without a Cursor


The first thing I will do is create a System-Versioned table in SQL Server 2016.  Before creating the table, I will determine if the object already exists and if true I will drop the table. If you have an interest in system-versioned (temporal tables) note the alter table statement before I drop the object.


IF OBJECT_ID('WHILELOOPTEST') is not null
BEGIN
ALTER TABLE  [dbo].[WHILELOOPTEST] SET ( SYSTEM_VERSIONING = OFF  )
DROP TABLE   [dbo].WHILELOOPTEST
END

Next, I create the system versioned table so we can see the number changes caused by the following WHILE loop.

Create TABLE WHILELOOPTEST
(
ProductPK int identity(1,1) primary key,
Product_Cost money,
Start_Date datetime2 generated always as row start hidden NOT NULL ,
End_Date   datetime2 generated always as row end hidden NOT NULL,
PERIOD FOR SYSTEM_TIME(Start_Date, End_Date)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WHILELOOPTESTHISTORY));

/** Insert Test Values  **/
INSERT INTO WHILELOOPTEST(Product_Cost)
values (2.50), (3.5), (9.0), (10.0)

/** View data before loop **/
SELECT *
FROM WHILELOOPTEST;


For those not familiar with while loops in SQL Server, it is important to know the WHILE loop is used to do an action until a condition is met.  That can be a very obtuse explanation which is why an example is helpful.  The first thing that happens in a WHILE loop is a Boolean (true or false) statement is evaluated.  In the example case the average of the product cost compared to the number 5000.  If the average is less than 5000, the statement is true and the logic which follows is executed. In the example the Product Cost is multiplied by 2 and updated.


WHILE (SELECT avg(Product_Cost) from WHILELOOPTEST) < 5000
BEGIN
update WHILELOOPTEST
set Product_Cost = Product_Cost * 2;
END

One of the most important things to remember when using a WHILE loop in any language is to ensure the condition will eventually evaluate to false. Otherwise you will end up in an unending, infinite loop.

/**Show Table Data **/
SELECT *
FROM WHILELOOPTEST;

It is possible to test within the logic of the WHILE loop for conditions as well and if the condition is met it is possible to break out of the loop.  In the second example the loop is broken if greatest Product Cost is great than 5000.  The WHILE loop actually ends up creating values greater than 5000 because the condition is evaluated before the update logic, so testing if the greatest value is greater than 5000 ensures the values do not go too high.  Of course this example is arbitrary, but in our day to day business processes this option to break could be really useful.

/** WHILE WITH CONDITIONAL BREAK **/
/** DROP TABLE IF EXISTS **/
IF OBJECT_ID('WHILELOOPTEST2') is not null
BEGIN
ALTER TABLE  [dbo].[WHILELOOPTEST2] SET ( SYSTEM_VERSIONING = OFF  )
DROP TABLE   [dbo].WHILELOOPTEST2
END

/**Create Table **/
Create TABLE WHILELOOPTEST2
(
ProductPK int identity(1,1) primary key,
Product_Cost money,
Start_Date datetime2 generated always as row start hidden,
End_Date   datetime2 generated always as row end hidden,
PERIOD FOR SYSTEM_TIME(Start_Date, End_Date)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WHILELOOPTESTHISTORY2));

/** Insert values into table **/
INSERT INTO WHILELOOPTEST2(Product_Cost)
values (2.50), (3.5), (9.0), (10.0)

/** observe values**/
SELECT *
FROM WHILELOOPTEST2;

/** WHILE LOOP WITH BREAK CONDITION **/
WHILE (SELECT AVG(Product_Cost) from WHILELOOPTEST2) < 5000
BEGIN
if(select TOP(1) Product_Cost from WHILELOOPTEST2 order by product_cost desc) > 5000
Break
update WHILELOOPTEST2
set Product_Cost = Product_Cost * 2

END;


Finally, because we used system versioned tables, we can actually look at the changes made by the WHILE loop.  Notice there are many updates per record which exceed the number of updates which might be expected.  The point to be taken away is that a WHILE loop can be much more expensive than it appears on the surface especially since SQL is optimized for set based operations.  The WHILE loop is included and has its uses but use the WHILE loop cautiously.

/**Compare data sets with and without break **/
SELECT *
FROM WHILELOOPTEST
FOR SYSTEM_TIME ALL
order by ProductPK, Product_Cost;

SELECT *
FROM WHILELOOPTEST2
FOR SYSTEM_TIME ALL
order by ProductPK, Product_Cost;

Popular posts from this blog

Power BI Report Server: "An Error Has Occurred" or Power BI Report Server: "My Power BI Report is Coming up Blank" or Power BI Report Server: "401 and 403” Errors From Nowhere

Image
The information below is an extremely brief summary of the information collected to solve the elusive problem.  There are points I left out because I could not find supporting documentation and I did not want to release suppositions into the universe (why does an NTLM only issue impact a system using kerberos?).  I also want to thank my friend Josh Corrick for staying the course and seeing this through to the end.  See Josh's blog here:  https://corrick.io/blog/ Without warning, our Power BI Report Server began having issues presenting Power BI reports.   The reports would appear to render but would eventually return a blank report canvas.   There was not an error presented and this was impacting all Power BI reports.   An important note is the Power BI reports had worked as expected previously and no changes had been made to the report server such as upgrades or configuration changes. Subsequently, and seemingly unrelated, an error notification was presented when clicking
Read more

SQL SERVER 2017: STRING_AGG

STRING_AGG is a function released in SQL Server 2017.   The Oracle equivalent LIST_AGG has been available since 11gR2.  In the meantime, SQL Server users have resorted to black magic in order to get string values from separate rows. STRING_AGG is very welcome but there are some gotchas to be aware of.  Below is a simple example that works just like you think it would. SELECT STRING_AGG(FIrst_Name, ', ') WITHIN GROUP (ORDER BY First_Name) LIST FROM             (SELECT DISTINCT   First_name FROM [dbo].[Employee]     WHERE First_Name like 'A%' OR First_Name like 'B%') A ORDER BY LIST; You can run the query on any database you have string values and you will get something like this: A David, Aaren, Aaron, Abdul, Bob, Brian... If you want to split by the first letter of the name, do not make the mistake of using an over clause.  You will get an error stating STRING_AGG is not a window function. The function 'STRING_AGG' is not a valid win
Read more

Dynamic Label Positions With SSRS

Image
On a recent tabular "dashboard" with several bar charts in a small area our team found vertical positioning of the labels to be troublesome.  When the labels were set to "bottom" and the bar was too short the label would truncate the significant digits.  On the other hand, when the labels were set to "top" the numbers would be truncated if the column was too tall. Example of bar chart with label set to bottom. Same chart with the middle column label set to top. First we thought about using Smart Labels  but we quickly found that smart labels are not so smart when the labels are turned 90 degrees to make them vertical.  As you can see in the examples above the size of the numbers would make horizontal labels messy. So the obvious choice was to use a function to place the label on top when the bar was small and on the bottom once the bar was tall enough. In Report Builder, go to your chart and select a specific column - not just the chart.
Read more