/* While Loop */
-- 1 : Intializer.
DECLARE @index int
SET @index =1
-- 2 : While with Condition.
while @index<=10
Begin
-- Your Command Here.
print 'Printed Number index is :'+ Convert(char(2),@index)
set @index=@index+1 -- Incrementer.
End
/* Using BREAK and CONTINUE */
-- 1 : Intializer.
Declare @index int
set @index =0
-- 2 : While with Condition.
while @index<=10
begin
set @index=@index+1 -- Increment.
IF @index=3
BEGIN
continue
END
ELSE
BEGIN
-- Your Command Here.
print 'Printed Number index is :'+ Convert(char(2),@index)
END
end
Result :
Printed Number index is :1
Printed Number index is :2
Printed Number index is :4
Printed Number index is :5
Printed Number index is :6
Printed Number index is :7
Printed Number index is :8
Printed Number index is :9
Printed Number index is :10
Printed Number index is :11
-- Usage of While stataement with BREAK
-- 1 : Intializer.
Declare @index int
set @index =0
-- 2 : While with Condition.
while @index<=10
begin
set @index=@index+1 -- Increment.
IF @index=3
BEGIN
break
END
ELSE
BEGIN
-- Your Command Here.
print 'Printed Number index is :'+ Convert(char(2),@index)
END
end
GO
/* Using Pubs Database */
Use Pubs
-- SELECT the values FROM titles
SELECT * FROM titles
--SELECT values into the temptitles FROM titles
SELECT * into temptitles
FROM titles
GO
-- Check for the Values in the temptitles
SELECT * FROM temptitles
--Check for average of price FROM the temptitles table
SELECT AVG(price + 2) FROM temptitles
-- Increase the price by 2 till the average of price is less tnan 20
while (SELECT AVG(price + 2) FROM temptitles) < 20
BEGIN
UPDATE temptitles
SET price = price + 2
END
GO
Select* from temptitles
/* Check */
SELECT AVG(price) FROM temptitles
-- To write a query so as to increase the price by 2 till average of price is not more
-- than 20 and at the same time MAXimum price is not more than 40
-- Check for the MAXimum price
SELECT MAX(price) FROM temptitles
-- While statement
WHILE (SELECT AVG(price) FROM temptitles) < 20
BEGIN
UPDATE temptitles
SET price = price + 2
IF (SELECT MAX(price) FROM temptitles) > 40
BREAK
ELSE
IF (SELECT AVG(price) FROM temptitles) < 20
CONTINUE
END
GO
/* Check */
SELECT AVG(price) FROM temptitles
SELECT MAX(price) FROM temptitles
/* Goto */
--goto will branch to a user defined label
DECLARE @err int
SET @err = 0
IF @err = 0
BEGIN
PRINT 'Branching to ......error label'
goto error
END
--Errors handling
error:
BEGIN
PRINT 'In Error label Execution'
END
GO
/* Return */
use pubs
IF not exists (SELECT * FROM titles WHERE title_id = 'B001')
BEGIN
PRINT 'No such title'
Return
END
SELECT * FROM publishers
GO
/* Wait for */
/*
Blocks the execution of a batch, stored procedure,
or transaction until a specified time or time interval is reached,
or a specified statement modifies or returns at least one row.
*/
DECLARE @ctr int
SET @ctr = 1
while @ctr < 10
BEGIN
waitfor delay '000:00:01'
PRINT 'Delayed by 1 Second'
SET @ctr = @ctr + 1
END
SELECT * FROM titles
