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, 255👍, 0💬
How to run Queries with SQL Server Management Studio Express in SQL Server? 1. Launch and connect SQ...
How To Create a Simple Stored Procedure in SQL Server Transact-SQL? If you want to create a simple s...
Where to find answers to frequently asked questions on Downloading and Installing SQL Server 2005 Ex...
How To Change the Name of a Database User in SQL Server? If you want to change the name of an existi...
Where to find answers to frequently asked questions on Managing Security, Login and User in SQL Serv...