BREAK of Loop Statement in SQL Server Transact-SQL
How to break a WHILE look statement in SQL Server Transact-SQL? How to use BREAK statements?
You can use the BREAK statement to break a WHILE loop inside the statement block in Transact-SQL using this syntax:
WHILE condition BEGIN statement_1 statement_2 ... BREAK; statement_n ... END
When a BREAK statement inside a WHILE loop is executed, the system will:
Here is an example on how to use BREAK statements inside WHILE loops:
DECLARE @count INT = 1, @sum INT = 0; WHILE 1=1 BEGIN SET @sum = @sum + @count; SET @count = @count+1; IF @count > 10 BREAK; END PRINT 'Sum of 1 to 10: ' + CAST(@sum AS CHAR);
Here is another example on how to use a BREAK statement to stop a WHILE loop early:
-- Counting number of days in 2000 DECLARE @date DATETIME; DECLARE @count INT; SET @date = '2000-01-01'; SET @count = 0; WHILE 1=1 BEGIN IF DATEPART(YEAR, @date) > 2000 BREAK; SET @count = @count + 1; SET @date = DATEADD(DAY, 1, @date); END SELECT @count; 366 -- 2000 is a leap year!
2017-01-11, 1839👍, 0💬
How To Set Up SQL*Plus Output Format in Oracle? If you want to practice SQL statements with SQL*Plus...
What Are Date and Time Functions in MySQL? MySQL offers a number of functions for date and time valu...
How To Drop an Index in Oracle? If you don't need an existing index any more, you should delete it w...
Where to find answers to frequently asked questions on Storage Engines: MyISAM, InnoDB and BDB in My...
What Is ISAM in MySQL? ISAM (Indexed Sequential Access Method) was developed by IBM to store and ret...