Looping S.P. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Looping S.P.

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-04-30 : 10:03:24

I have not worked with looping S.P. Before and must be screwing up. It,s probably something simple and my brain is not functioning correctly this morning. Anyone see where I went wrong?

CREATE PROCEDURE dbo.ZZZZInvLocationCreate
AS
WHILE (Max(dbo.InvLocations.LocationID) < 10501198)

INSERT INTO dbo.InvLocations
(LocationID)
SELECT MAX(LocationID) + 1 AS Expr1
FROM dbo.InvLocations

CONTINUE
GO

Jim

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 10:31:25

The while syntax is:

WHILE {Condition} {Statement}

It repeats only 1 statement over and over, while {condition} is true.

Of course, you rarely want to repeat only one statement, so you can repalce that statement with a group of statements -- if you surround them by a BEGIN and an END. Exactly the same as IF statements, and like using {} in java or C.

so, in your case, only the first statement is being repeated in the loop. you need to surround all of the statements you want to repeat into a BEGIN and END group, effectively turning that sequence of statements into 1 big statement and thus allowing the WHILE loop to repeat all of them.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-30 : 10:53:59

You only use to CONTINUE syntax to skip all other logic and skip back to the top....your code would look like:


CREATE PROCEDURE dbo.ZZZZInvLocationCreate 
AS 
	WHILE (Max(dbo.InvLocations.LocationID) < 10501198) 
		BEGIN
			INSERT INTO dbo.InvLocations (LocationID) 
			     SELECT MAX(LocationID) + 1 AS Expr1 
			       FROM dbo.InvLocations
		END

But I must point out that you have NO predicate in your INSERT. So every time you loop, you will be inserting EVERY row back in to your table. And then it will get bigger by 2 times everytime th loop is executed.

Also your WHILE is malformed and I don't think this will compile.

I think you need:


	     WHILE (SELECT MAX(LocationID) FROM dbo.InvLocations) < 10501198

But don't hold me to it because the whole doesn't make sense to me.

Let us know what you're trying to accomplish.

Thanks

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 11:07:58

Makes sense ... looks like you are just starting with the MAX(locationID), adding 1 to it, and adding rows to that table essentially adding new locationID's until you get to 10501198.

FYI -- Here is a more efficeint way to do it:

declare @i int;
set @i = (select max(locationID) from Invlocations) + 1
while @i < 10501198
begin
insert into invlocations (locationID) values (@i)
set @i = @i + 1
end

An even BETTER way to do it would be to use a TALLY table (do a search for more info on those if you haven't seen them yet).

Assuming the view/table Tally returns numbers from 1..99999999:

set @i = (select max(locationID) from invlocations)
insert into invlocations (locationID)
select @i + Tally.n
from
tally
where
tally.n < 10501198 - @i

- Jeff

Edited by - jsmith8858 on 04/30/2003 11:23:45

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-30 : 11:25:23

Jeff,

quote:

Makes sense

quote:


INSERT INTO dbo.InvLocations (LocationID) 
			     SELECT MAX(LocationID) + 1 AS Expr1 
			       FROM dbo.InvLocations

Is that what you're refering to?

Brett

8-)

Edited by - x002548 on 04/30/2003 11:25:54

Edited by - x002548 on 04/30/2003 11:26:21

Edited by - x002548 on 04/30/2003 11:26:44

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 11:30:56

Yeah ... it makes logical sense what he is trying to do, and his method might even work (haven't tried it). the examples I gave would be much more efficient, though.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-30 : 11:39:58

[homer]
doooohh
[/homer]

I don't know what I was thinking..

quote:

So every time you loop, you will be inserting EVERY row

[bugs bunny]
What a maroon
[/bugs bunny]

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-04-30 : 12:16:57

ALTER PROCEDURE dbo.ZZZZInvLocationCreate
AS
WHILE (SELECT MAX(LocationID) FROM dbo.InvLocations) < 10501198

BEGIN
INSERT INTO dbo.InvLocations (LocationID,arrow)
SELECT MAX(LocationID) + 1 AS Expr1,1
FROM dbo.InvLocations
END

Works Great brett!!!!