March 10, 2009 at 7:56 am
I don't know what is wrong with my process below, but it ends up in an endless loop.
So, if anyone could please help review the code I have below and see where I went wrong
that it results in an endless loop would be appreciated. Or if there is a better way to
do this, please advise.
I wanted to add 2 records (or howeever many that result from the count below) with the
CompanyLocID (manually increase by 1) and the shippingID also increased by one
by its own increasing stored procedure as it inserting the nubmer of records.
This is not a good way to do auto increase like this, but I am not allowed to change
the table structure in table test1. Also, the @nextshippingid generate null instead of
the next number. But if I execute the procedure itself then it generate the number fine.
create table test1
(
companyLocID int,
shippingID int,
status int
)
create table tbGetNumber
(
pkid int identity (1,1) not null,
shippingID int,
shippingIDused int
)
insert into test1
select 1, 10, 1
union
select 2, 20, 1
union
select 3, 30, 2
union
select 4, 40, 2
union
select 5, 50, 3
insert into tbGetNumber(shippingID, shippingIDused)
values (100,200)
alter procedure dbo.GetNextNumber
@nextNumber int OUTPUT AS
declare @nextNum int
begin
select @nextNum = shippingID
from tbGetNumber
update tbGetNumber
set shippingID = (@nextNum+1)
end
drop table test1
drop table tbGetNumber
alter procedure insertrecord
as
declare @count int
set @count = (select count(*) from test1 where status = 2)
if @count > 0
begin
declare @lastcompanylocID int
declare @maxcompanylocid int
declare @nextcompanylocid int
set@lastcompanylocid = (select max(companylocid) from test1)
set @maxcompanylocid = @lastcompanylocid + @count
set @nextcompanylocid = @lastcompanylocid + 1
declare @nextshippingid int
EXEC GetNextNumber @nextshippingid OUTPUT
while @nextcompanylocid <= @maxcompanylocid
begin
insert into test1
select @nextcompanylocid, @nextshippingid, 1
from test1
where status = 2
end
set @nextcompanylocid = @nextcompanylocid + 1
end
--select * from test1
--exec insertrecord
--
--delete test1
--where companylocid = 6
March 10, 2009 at 8:10 am
You need to move this:
set @nextcompanylocid = @nextcompanylocid + 1
Needs to be before the END for the While loop.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 10, 2009 at 8:19 am
GSquared, thanks a lot. You're right. The loop is ended correctly now. I still have the other variable insert null, but I think I probably need to do another loop for it like the others.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply