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

SET @LoopMaxCount = 0    /* Intentionally set to zero */

WHILE (1 = 1)    /* Give an expression, outcome of which is always true */
    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 ;


Following screenshot shows the output of the above query.

Example of DO-WHILE Loop in T-SQL

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.

We were able to achieve the DO-WHILE Loop functionality using just the WHILE Loop, IF Statement, and the BREAK Statement.

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.


About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

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 , , , , , , , , , , . Bookmark the permalink. Leave a comment.

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: