|
Can anyone tell me why I cannot create a procedure with the following code in SQL Server 2000? For example I can run this in SQL Query Analyzer and it works:
WHILE
(SELECT count(*) FROM testtable) > 0
BEGIN
UPDATE testtable SET test_col = 'UPDATED'
IF (SELECT count(*) FROM testtable) = 0
BREAK
ELSE
CONTINUE
END
However whenever I add the line CREATE PROCEDURE TEST to create a procedure from the working code above it always fails with error 'Incorrect syntax near the keyword 'WHILE'. i.e.
CREATE PROCEDURE TEST
WHILE
(SELECT count(*) FROM testtable) > 0
BEGIN
UPDATE testtable SET test_col = 'UPDATED'
IF (SELECT count(*) FROM testtable) = 0
BREAK
ELSE
CONTINUE
END
Server: Msg 156, Level 15, State 1, Procedure test, Line 2 Thanks in advance.
(comments are locked)
|
|
You need an AS.. Thanks all just noticed it.
Nov 25 '09 at 12:28 PM
David 2 1
(comments are locked)
|
|
The logic does not seem to good without WHERE clause. If there is no where clause you can simply use
(comments are locked)
|

