If and While Statement 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

-- While statement
print 'While statement'
DECLARE @countnumber varchar(50)
SET @countnumber  = 1
WHILE (@countnumber<=30)
BEGIN
PRINT 'Number=' +  @countnumber
SET @countnumber = @countnumber  + 3
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.

-- While Statement with Break
print 'While Statement with break'
DECLARE @countnumber varchar(50)
SET @countnumber  = 1
WHILE (@countnumber<=30)
BEGIN
PRINT 'Number=' +  @countnumber
SET @countnumber = @countnumber  + 3
if(@countnumber=22)
break
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.

-- While Statement with continue
print 'While Statement with continue'
DECLARE @countnumber varchar(50)
SET @countnumber  = 1
WHILE (@countnumber<=30)
BEGIN
PRINT 'Number=' +  @countnumber
SET @countnumber = @countnumber  + 3
CONTINUE;
if(@countnumber=4)  -- This will never execute.
break
END

Output of the preceding examples in SQL Server Management Studio

 

Now run the preceding examples in SQL Server Management Studio.

-- While statement
print 'While statement'
DECLARE @countnumber varchar(50)
SET @countnumber  = 1
WHILE (@countnumber<=30)
BEGIN
PRINT 'Number=' +  @countnumber
SET @countnumber = @countnumber  + 3
END
 go

-- While Statement with Break
print 'While Statement with break'
DECLARE @countnumber varchar(50)
SET @countnumber  = 1
WHILE (@countnumber<=30)
BEGIN
PRINT 'Number=' +  @countnumber
SET @countnumber = @countnumber  + 3
if(@countnumber=22)
break
END
go

-- While Statement with continue
print 'While Statement with continue'
DECLARE @countnumber varchar(50)
SET @countnumber  = 1
WHILE (@countnumber<=30)
BEGIN
PRINT 'Number=' +  @countnumber
SET @countnumber = @countnumber  + 3
CONTINUE;
if(@countnumber=4)  -- This will never execute.
break
END

Output

 

 

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

create table UsingIF
(
price int,
Type varchar(20),
Title varchar(100)
);
go
insert into UsingIF values('20','cold','rasnel hussy this is the title')
go
select * from UsingIF;

Output

 

 

 

Example

IF (SELECTAVG(price)FROM UsingIF WHEREtype ='cold')<= 15
BEGIN
  PRINT 'The following titles are excellent cold books:'
  PRINT ' '
  SELECT SUBSTRING(title, 1, 35)AS Title
  FROM UsingIF
  WHERE type= 'cold'
END
ELSE
  PRINT 'Average price is more than 15.'

Output