What’s WHILE?
Unlike Data Definition Language statements for creating views, tables or indexes, query statements for inserting and retrieving data into these structures, the WHILE keyword is part of the flow control. This means that this keyword can be used in SQL statements to control processes and execute certain queries multiple times.
How can you use WHILE statements?
WHILE statements are especially useful if you want to execute a task multiple times, i.e. until a certain condition is met. To demonstrate how this works, let’s start by creating a small table that shows how deadly different ninjas of our dojo are:
DROP TABLE IF EXISTS #NinjaDemo
CREATE TABLE #NinjaDemo
(
NinjaId INT IDENTITY(1,1)
,Deadlyness float NOT NULL
)
Now we want to fill this table with the first five ninjas, but since we only use demo records, to protect the identity of our ninjas, we fill the Deadliness column with random numbers. We can then do this as follows:
INSERT INTO #NinjaDemo (Deadlyness) VALUES (RAND()) ,(RAND()) ,(RAND()) ,(RAND()) ,(RAND())
If we now look at the records, we see five entries with random values for deadlyness. But if we want to do this for a larger dojo and need 200 entries instead of the five ninjas, we could either copy and paste a very long (and confusing) statement or we could use a WHILE statement to create the records. This would be done as follows:
DECLARE @nRows int = 0;
SELECT @nRows = COUNT(*) FROM #NinjaDemo
WHILE @nRows < 200
BEGIN
INSERT INTO #NinjaDemo (Deadlyness) VALUES (RAND())
SELECT @nRows = COUNT(*) FROM #NinjaDemo
END
SELECT * FROM #NinjaDemo
Now the table has 200 entries, but if we want 500 or 5000 entries instead, we would only have to change a number in the WHILE condition in this statement. But to fill large tables with random numbers, a separate language construct would certainly not be worth it. The WHILE statement can of course do more than that. Suppose we want to rebuild all clustered indexes in our database. Let's first write a query that lists all clustered indexes:
SELECT
i.[name] as index_name
,schema_name(t.schema_id) + '.' + t.[name] as table_view
FROM sys.objects t
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
WHERE t.[type] = 'U'
AND i.[type] = 1
To rebuild all these indexes, we generate dynamic SQL statements that we want to execute - ATTENTION, of course you should try this in the Dojo before you compete in the real competition (or don't execute it on production servers!). We can do this with a WHILE statement, by always rebuilding an index until there is no index to rebuild. To do this we first create a temporary table with the indexes we want to rebuild:
DROP TABLE IF EXISTS #sqlReorg
SELECT
'ALTER INDEX ['+i.[name]+'] ON ['+schema_name(t.schema_id) + '].[' + t.[name]+'] REORGANIZE ;' as Stmt
INTO #sqlReorg FROM sys.objects t
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
WHERE t.[type] = 'U'
AND i.[type] = 1
Now we fetch an index from this table, generate the corresponding statement and delete the index from the table:
DECLARE @stmt NVARCHAR(500)
WHILE (SELECT COUNT(*) FROM #sqlReorg) > 0
BEGIN
SELECT top(1) @stmt = [stmt] FROM #sqlReorg
DELETE FROM #sqlReorg WHERE [Stmt] = @stmt
PRINT 'Running '+@stmt
EXEC sp_Executesql @stmt
END
This is not best practice, of course, as it does not include error handling, but it shows how the WHILE statement can be used to execute multiple statements. Important: SQL is always efficient when working with data sets. By definition, WHILE statements work with single records first, so what you do here is potentially always a slow solution to your problem. Often there are better and more efficient solutions, so before you start with a WHILE statement, think carefully if this is really the best solution and if you can somehow allow the server to work efficiently with multiple records...
