Trouble Creating Procedure With While Loop

Question

question

David 2 1 asked Nov 25, '09

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-sql sql-server-2000 stored-procedures

10 |1200

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

Answer 1 · 2009-11-25T08:40:21Z

RickD answered Nov 25, '09

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

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

Answer 2 · 2009-11-25T09:39:29Z

Madhivanan answered Nov 25, '09

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

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

question