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, 1066👍, 0💬
How to change the data type of an existing column with "ALTER TABLE" statements in SQL Server? Somet...
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...
What is dba.FYIcenter.com Website about? dba.FYIcenter.com is a Website for DBAs (database administr...
What Happens to Your Transactions When ERROR 1213 Occurred in MySQL? If your transaction receives th...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...