SQL Server – Implementing a DO-WHILE Loop in Transact-SQL
Last day there was a T-SQL Session going on with a bunch of folks attending the session and most of them were familiar with T-SQL. During the session, the topic of Transact-SQL WHILE Loop Construct came up and we started discussing about it. Suddenly I asked everyone, How to Implement DO-WHILE Loop in T-SQL. To my surprise, many folks were not able to answer this question. So just thought that this can be good learning for folks who are not familiar with the implementation of DO-WHILE in T-SQL and also this tip is worth adding to the T-SQL Interview Questions and Answers List.
A DO-WHILE Loop is a Looping Construct, similar to WHILE Loop. The difference between WHILE Loop and DO-WHILE Loop is that, statements inside a WHILE Loop are executed only if the condition specified in the WHILE Loop is satisfied, whereas the statements inside a DO-WHILE Loop are executed at least once irrespective of whether the condition specified in the DO-WHILE Loop is satisfied or not.
As most of you, who are reading this article might already be aware and those of you who are new to this, there is no DO-WHILE Looping Construct available in T-SQL Language. However, we can achieve the DO-WHILE Looping functionality in T-SQL by combining a couple of T-SQL Control Flow Clauses/Constructs/Statements.
Let us say that you want to achieve a DO-WHILE Loop functionality and the statements inside the loop should execute specified number of times based on the condition but should execute at least once. Below is an example of the same.
DECLARE @LoopCounter TINYINT = 1 DECLARE @LoopMaxCount TINYINT SET @LoopMaxCount = 0 /* Intentionally set to zero */ WHILE (1 = 1) /* Give an expression, outcome of which is always true */ BEGIN PRINT 'Loop Counter Value: ' + CAST(@LoopCounter AS VARCHAR(3)) /* Your list of statements to be executed here. */ SET @LoopCounter = @LoopCounter + 1 ; IF (@LoopCounter > @LoopMaxCount) BREAK ; END GO
Following screenshot shows the output of the above query.
As you can see from the above output, even though the condition specified in the IF Statement at the end of WHILE Loop is false during the first time check itself, still the statements inside WHILE Loop Executed Once.
Have you ever come across a need to implement DO-WHILE Loop in T-SQL? If yes, how did you implement it? Do let me know by leaving a comment below.
Posted on June 21, 2012, in Code Snippets, Development Tips, Interview Questions, SQL Server, T-SQL, T-SQL Questions, T-SQL Snippets, T-SQL Tips, Tips 'N' Tricks and tagged Conditional (programming), Control Flow, Databases, Looping Constructs, SQL, SQL Constructs, SQL Scripts, SQL Server, T-SQL, Tips, Transact-SQL. Bookmark the permalink. Leave a comment.