Please navigate to the bottom of the page for Table of Contents

Monday, June 13, 2011

SQL IF-ELSE and WHILE examples

As like the conditional processing constructs, SQL flow control statements are a critical part of your understanding of SQL. You may be asked a direct question; most likely it will be a part of a larger question.

Transact-SQL provides special words called control-of-flow language that control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. These control-of-flow words are useful when you need to direct Transact-SQL to take some kind of action. For example, use a BEGIN...END pair of statements when including more than one Transact-SQL statement in a logical block. Use an IF...ELSE pair of statements when a certain statement or block of statements needs to be executed IF some condition is met, and another statement or block of statements should be executed if that condition is not met (the ELSE condition).

One interesting question that I have seen where even seasoned candidates’ trip is when asked to explain transaction rollbacks.

BEGIN TRANSACTION;
GO
IF @@TRANCOUNT = 0
BEGIN
SELECT FirstName, MiddleName
FROM Person.Person WHERE LastName = 'Adams';
ROLLBACK TRANSACTION;
PRINT N'Rolling back the transaction two times would cause an error.';
END;
ROLLBACK TRANSACTION;
PRINT N'Rolled back the transaction.';
GO
/*
Rolled back the tranaction.
*/


In the above example, BEGIN and END define a series of Transact-SQL statements that execute together. If the BEGIN...END block were not included, both ROLLBACK TRANSACTION statements would execute and both PRINT messages would be returned.


Using IF…ELSE


IF...ELSE evaluates a Boolean expression, and if TRUE, executes a Transact-SQL statement or batch. The syntax is simple:

IF Boolean_expression { sql_statement | statement_block } 
[ ELSE { sql_statement | statement_block } ]

Boolean_expression  -  Is an expression that returns TRUE or FALSE. If the Boolean_expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.

{ sql_statement | statement_block } - Is any valid Transact-SQL statement or statement grouping as defined with a statement block. To define a statement block (batch), use the control-of-flow language keywords BEGIN and END. Although all Transact-SQL statements are valid within a BEGIN...END block, certain Transact-SQL statements should not be grouped together within the same batch (statement block).



Using a simple Boolean expression

The following example has a simple Boolean expression (1=1) that is true and, therefore, prints the first statement.

IF 1 = 1 PRINT 'Boolean_expression is true.'
ELSE PRINT 'Boolean_expression is false.' ;


Using a query as part of a Boolean expression

The following example executes a query as part of the Boolean expression.

IF 
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
PRINT 'There are more than 5 Touring-3000 bicycles.'
ELSE
PRINT 'There are 5 or less Touring-3000 bicycles.' ;


Using nested IF...ELSE statements

The following example shows how an IF … ELSE statement can be nested inside another. Set the @Number variable to 5, 50, and 500 to test each statement.

DECLARE @Number int;
SET @Number = 50;
IF @Number > 100
PRINT 'The number is large.';
ELSE
BEGIN
IF @Number < 10
PRINT 'The number is small.';
ELSE
PRINT 'The number is medium.';
END ;
GO


Using WHILE statement


WHILE allows you to set a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.

Using BREAK and CONTINUE with nested IF...ELSE and WHILE

In the following example, if the average list price of a product is less than $300, the WHILE loop doubles the prices and then selects the maximum price. If the maximum price is less than or equal to $500, the WHILE loop restarts and doubles the prices again. This loop continues doubling the prices until the maximum price is greater than $500, and then exits the WHILE loop and prints a message.

WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300
BEGIN
UPDATE Production.Product
SET ListPrice = ListPrice * 2
SELECT MAX(ListPrice) FROM Production.Product
IF (SELECT MAX(ListPrice) FROM Production.Product) > $500
BREAK
ELSE
CONTINUE
END
PRINT 'Too much for the market to bear';

17 comments:

  1. This was so useful and informative. The article helped me to learn something new. By play barbie games

    ReplyDelete
  2. Grate help for Beginners.
    Thanks to Nikhil Sir :)

    ReplyDelete
  3. The blog gave me idea about if else and while Thanks for sharing it
    Java training in chennai

    ReplyDelete
  4. There is a different scope of languages accessible and appropriate for Web programming. easy programming class Livermore

    ReplyDelete
  5. Excellent post. I always check this type of blog, and I’m impressed with Extremely useful info specially the last part, I care for such information a lot. I am exploring this particular info for a long time. Thanks to this blog my exploration has ended. python internship in jaipur

    ReplyDelete
  6. Effective blog with a lot of information. I just Shared you the link below for Courses .They really provide good level of training and Placement,I just Had Oracle Classes in this institute,Just Check This Link You can get it more information about the Oracle course.


    Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

    ReplyDelete
  7. Nice blog. Thank you for sharing. The information you shared is very effective for learners I have got some important suggestions from itThanks for sharing an informative blog keep rocking bring more details.I like the helpful info you provide in your articles. I’ll bookmark your weblog and check again here regularly
    Java training in Chennai

    Java Online training in Chennai

    Java Course in Chennai

    Best JAVA Training Institutes in Chennai

    Java training in Bangalore

    Java training in Hyderabad

    Java Training in Coimbatore

    Java Training

    Java Online Training

    ReplyDelete
  8. wow really nice. It will be helpful for the people those who are ready to crack the interview and please also for remind what they have learned throughout concept.This concept is a good way to enhance the knowledge.thanks for sharing.
    DevOps Training in Chennai

    DevOps Online Training in Chennai

    DevOps Training in Bangalore

    DevOps Training in Hyderabad

    DevOps Training in Coimbatore

    DevOps Training

    DevOps Online Training

    ReplyDelete
  9. Thanks for provide great informatics and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you.
    Data Science Training In Chennai

    Data Science Online Training In Chennai

    Data Science Training In Bangalore

    Data Science Training In Hyderabad

    Data Science Training In Coimbatore

    Data Science Training

    Data Science Online Training

    ReplyDelete
  10. Hi Dear, have you been certainly visiting this site daily, if that's the case you then will certainly get good knowledge.
    logo companies designer

    ReplyDelete
  11. I appreciate something you have acquainted with my skill base.Admiring the arise as outdated and exertion you put into your weblog and nitty gritty reference you pay for.thanks. Farming Simulator Free Download

    ReplyDelete
  12. Congrats on the appearance of your delightful child. Inform us as to whether you want anything - we're here to help! The very best to your developing! Thums Up Baby Wishes

    ReplyDelete