SQL Error: 'Invalid SQL Statement'

break is for a while loop:

WHILE
Sets a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.

Syntax
WHILE Boolean_expression
    { sql_statement | statement_block }
    [ BREAK ]
    { sql_statement | statement_block }
    [ CONTINUE ]

Arguments
Boolean_expression

Is an expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.

{sql_statement | statement_block}

Is any Transact-SQL statement or statement grouping as defined with a statement block. To define a statement block, use the control-of-flow keywords BEGIN and END.

BREAK

Causes an exit from the innermost WHILE loop. Any statements appearing after the END keyword, marking the end of the loop, are executed.

CONTINUE

Causes the WHILE loop to restart, ignoring any statements after the CONTINUE keyword.

Remarks
If two or more WHILE loops are nested, the inner BREAK exits to the next outermost loop. First, all the statements after the end of the inner loop run, and then the next outermost loop restarts.

Examples
A. Use BREAK and CONTINUE with nested IF...ELSE and WHILE
In this example, if the average price is less than $30, the WHILE loop doubles the prices and then selects the maximum price. If the maximum price is less than or equal to $50, the WHILE loop restarts and doubles the prices again. This loop continues doubling the prices until the maximum price is greater than $50, and then exits the WHILE loop and prints a message.

USE pubs
GO
WHILE (SELECT AVG(price) FROM titles) < $30
BEGIN
   UPDATE titles
      SET price = price * 2
   SELECT MAX(price) FROM titles
   IF (SELECT MAX(price) FROM titles) > $50
      BREAK
   ELSE
      CONTINUE
END
PRINT 'Too much for the market to bear'

B. Using WHILE within a procedure with cursors
The following WHILE construct is a section of a procedure named count_all_rows. For this example, this WHILE construct tests the return value of @@FETCH_STATUS, a function used with cursors. Because @@FETCH_STATUS may return -2, -1, or 0, all three cases must be tested. If a row is deleted from the cursor results since the time this stored procedure was executed, that row is skipped. A successful fetch (0) causes the SELECT within the BEGIN...END loop to execute.

USE pubs
DECLARE tnames_cursor CURSOR
FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
--SET @tablename = 'authors'
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN  
      SELECT @tablename = RTRIM(@tablename)
      EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '
            + @tablename )
      PRINT ' '
   END
   FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

You are correct to say that BREAK is a valid SQL statement - and TAshby shows you the correct usage of this word. But you are trying to use BREAK ON which is Oracle specific syntax.

To duplicate the output of the BREAK ON syntax in MSSQL, check out BOL for the ROLLUP keyword.

hth

In Oracle, BREAK ON is not a valid SQL DML command. It a SQL PLUS command. Using for formatting the Select output records.

100% for sure you can not use it with ODBC.

I can't find any documantation on BOL or ROLLUP.  Can u give me a link?

alzoid69,
No comment has been added lately (214 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: Award points to brettc http:#8041648

Please leave any comments here within 7 days.

-- Please DO NOT accept this comment as an answer ! --

Thanks,

knowlton
EE Cleanup Volunteer