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';

26 comments:

  1. Replies
    1. Big data is a term that describes the large volume of data – both structured and unstructured – that inundates a business on a day-to-day basis. big data projects for students But it’s not the amount of data that’s important.Project Center in Chennai

      Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Corporate TRaining Spring Framework the authors explore the idea of using Java in Big Data platforms.

      Spring Training in Chennai

      The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

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

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

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

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

    ReplyDelete
  6. 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
  7. 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
  8. Thanks 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. I am quite sure I will learn much new stuff right here! Good luck for the next!
    Artificial Intelligence Training in Chennai

    Ai Training in Chennai

    Artificial Intelligence training in Bangalore

    Ai Training in Bangalore

    Artificial Intelligence Training in Hyderabad | Certification | ai training in hyderabad

    Artificial Intelligence Online Training

    Ai Online Training

    Blue Prism Training in Chennai

    ReplyDelete
  9. 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

  10. Great post!I am actually getting ready to across this information,i am very happy to this commands.Also great blog here with all of the valuable information you have.Well done,its a great knowledge

    Azure Training in Chennai

    Azure Training in Bangalore

    Azure Training in Hyderabad

    Azure Training in Pune

    Azure Training | microsoft azure certification | Azure Online Training Course

    Azure Online Training


    ReplyDelete
  11. 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
  12. Great site and a great topic as well I really get amazed to read this.This is incredible,I feel really happy to have seen your webpage.I gained many unknown information, the way you have clearly explained is really fantastic.keep posting such useful information.
    Full Stack Training in Chennai | Certification | Online Training Course
    Full Stack Training in Bangalore | Certification | Online Training Course

    Full Stack Training in Hyderabad | Certification | Online Training Course
    Full Stack Developer Training in Chennai | Mean Stack Developer Training in Chennai
    Full Stack Training

    Full Stack Online Training



    ReplyDelete
  13. 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
  14. I feel really happy to have seen your webpage.I am feeling grateful to read this.you gave a nice information for us.please updating more stuff content...keep up!!
    Android Training in Chennai

    Android Online Training in Chennai

    Android Training in Bangalore

    Android Training in Hyderabad

    Android Training in Coimbatore

    Android Training

    Android Online Training

    ReplyDelete