question

David 2 1 avatar image
David 2 1 asked

Trouble Creating Procedure With While Loop

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
'Incorrect syntax near the keyword 'WHILE'

Thanks in advance.

t-sqlsql-server-2000stored-procedures
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

RickD avatar image
RickD answered

You need an AS..

CREATE PROCEDURE TEST            
AS            
            
WHILE            
(SELECT count(*) FROM testtable) > 0            
            
BEGIN            
UPDATE testtable SET test_col = 'UPDATED'            
IF (SELECT count(*) FROM testtable) = 0            
BREAK            
ELSE            
CONTINUE            
END            
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Madhivanan avatar image
Madhivanan answered

The logic does not seem to good without WHERE clause. If there is no where clause you can simply use

CREATE PROCEDURE TEST            
AS            
            
UPDATE testtable SET test_col = 'UPDATED'            
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.