If and While Statement in SQL Server

In this article, we will see how to use If and While statements in SQL Server.

In this article, we will see how to use If and While statements in SQL Server. If you have programmed in languages like C# or other languages then you are probably familiar with If and While statements. Transact-SQL also gives you this option to repeat the expression using a While loop and check the condition with an If loop. So let's take a look at a practical example of If and While statements with break and continue into SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.  There are some simple steps to do that are described here.
 

While Statement in SQL Server

 
 If the expression is true, the statement is executed, and then the expression is evaluated again to determine if the statement should be executed again. This process repeats until the expression evaluates to false.
 
Syntax
 
The While loop syntax is as defined below:
 
WHILE bool_exp
{sql_statement}
[BREAK]
{sql_statement}
[CONTINUE]
 
In the preceding syntax if the bool_exp value is true then sql_statement will be executed. This process repeats until the expression evaluates to false.
 
Example
  1. -- While statement  
  2. print 'While statement'  
  3. DECLARE @countnumber varchar(50)  
  4. SET @countnumber  = 1  
  5. WHILE (@countnumber<=30)  
  6. BEGIN  
  7. PRINT 'Number=' +  @countnumber  
  8. SET @countnumber = @countnumber  + 3  
  9. END  
The execution of statements in the WHILE loop can be controlled from inside the loop with the break and continue keywords.
 

BREAK Keyword in SQL Server

 
Break forces a loop to exit immediately. Suppose we repeat a number from 1 to 20. The loop will go through all the numbers. We want to exit when we get to the number 11 in the loop. That can be done simply by using the break keyword with a while loop.
  1. -- While Statement with Break  
  2. print 'While Statement with break'  
  3. DECLARE @countnumber varchar(50)  
  4. SET @countnumber  = 1  
  5. WHILE (@countnumber<=30)  
  6. BEGIN  
  7. PRINT 'Number=' +  @countnumber  
  8. SET @countnumber = @countnumber  + 3  
  9. if(@countnumber=22)  
  10. break  
  11. END  

Continue Keyword in SQL Server

 
This does the opposite of break. Instead of terminating the loop, it immediately loops again, skipping the rest of the code. The continue statement skips the value and jumps to the while loop without terminating the loop.
  1. -- While Statement with continue  
  2. print 'While Statement with continue'  
  3. DECLARE @countnumber varchar(50)  
  4. SET @countnumber  = 1  
  5. WHILE (@countnumber<=30)  
  6. BEGIN  
  7. PRINT 'Number=' +  @countnumber  
  8. SET @countnumber = @countnumber  + 3  
  9. CONTINUE;  
  10. if(@countnumber=4)  -- This will never execute.  
  11. break  
  12. END  
Output of the preceding examples in SQL Server Management Studio
 
Now run the preceding examples in SQL Server Management Studio.
  1. -- While statement  
  2. print 'While statement'  
  3. DECLARE @countnumber varchar(50)  
  4. SET @countnumber  = 1  
  5. WHILE (@countnumber<=30)  
  6. BEGIN  
  7. PRINT 'Number=' +  @countnumber  
  8. SET @countnumber = @countnumber  + 3  
  9. END  
  10.  go  
  11.   
  12. -- While Statement with Break  
  13. print 'While Statement with break'  
  14. DECLARE @countnumber varchar(50)  
  15. SET @countnumber  = 1  
  16. WHILE (@countnumber<=30)  
  17. BEGIN  
  18. PRINT 'Number=' +  @countnumber  
  19. SET @countnumber = @countnumber  + 3  
  20. if(@countnumber=22)  
  21. break  
  22. END  
  23. go  
  24.   
  25. -- While Statement with continue  
  26. print 'While Statement with continue'  
  27. DECLARE @countnumber varchar(50)  
  28. SET @countnumber  = 1  
  29. WHILE (@countnumber<=30)  
  30. BEGIN  
  31. PRINT 'Number=' +  @countnumber  
  32. SET @countnumber = @countnumber  + 3  
  33. CONTINUE;  
  34. if(@countnumber=4)  -- This will never execute.  
  35. break  
  36. END  
Output
 
while statement in sql server
 

IF Statement in SQL Server

 
IF a Boolean expression which follows the keyword IF, evaluates to true then ____. If the IF statement contains an else statement, then a second group of SQL statements can be executed if the IF condition evaluates to false. The Transact-SQL statement following an IF keyword and its condition is executed if the condition is satisfied (when the Boolean expression returns TRUE). The optional ELSE keyword introduces an alternate Transact-SQL statement that is executed when the IF condition is not satisfied (when the Boolean expression returns FALSE).
 
Syntax
 
IF Bool_exp
{ sql_statement  }
[ ELSE
{ sql_statement } ] 
 
Creating table in SQL Server
  1. create table UsingIF  
  2. (  
  3. price int,  
  4. Type varchar(20),  
  5. Title varchar(100)  
  6. );  
  7. go  
  8. insert into UsingIF values('20','cold','rasnel hussy this is the title')  
  9. go  
  10. select * from UsingIF;  
Output
 
Table-in-sqlserver.jpg 
 
Example
  1. IF (SELECTAVG(price)FROM UsingIF WHEREtype ='cold')<= 15  
  2. BEGIN  
  3.   PRINT 'The following titles are excellent cold books:'  
  4.   PRINT ' '  
  5.   SELECT SUBSTRING(title, 1, 35)AS Title  
  6.   FROM UsingIF  
  7.   WHERE type= 'cold'  
  8. END  
  9. ELSE  
  10.   PRINT 'Average price is more than 15.'  
Output
 
If statement in sql server