x

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.

more ▼

asked Nov 25, 2009 at 06:51 AM in Default

avatar image

David 2 1
732 54 58 65

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Nov 25, 2009 at 08:40 AM

avatar image

RickD
1.7k 2 3 6

(comments are locked)
10|1200 characters needed characters left

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'

more ▼

answered Nov 25, 2009 at 09:39 AM

avatar image

Madhivanan
1.1k 2 5 9

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1066
x501
x455

asked: Nov 25, 2009 at 06:51 AM

Seen: 1845 times

Last Updated: Nov 25, 2009 at 09:00 AM

Copyright 2016 Redgate Software. Privacy Policy