November 16, 2004 at 10:55 am
Hi All,
Still using cursors, as I don't know any other way to accomplish this next task. I have two tables, one which is keyed with a GUID, the other which has a FK to the first and I'm trying to populate with some manipulated data:
Each switch location has 28 "VT"s, which follow the naming scheme of 1-1 through 1-4 thru 7-1 through 7-4
In this example, however, the middle of 3 WHILEs is not being executed - I'm ending up with just VT 1-1 through 1-4.
(is there any preferred means for presenting code snippets in this forum?)
Thanks
MVP
USE gcTelco
GO
DELETE FROM dbo.gcVTPorts
GO
DECLARE @PortKey uniqueidentifier
DECLARE @VTLeft int, @VTRight int, @NextVTPortID int
DECLARE @VTDescriptor varchar (50),@PortDescriptor varchar (50)
DECLARE VTPorts_Cursor CURSOR FOR
SELECT t.PortKey, t.PortDescriptor
FROM dbo.gcEquipmentPort t
WHERE (t.EquipmentID = 1)
ORDER BY t.PortDescriptor
OPEN VTPorts_Cursor
FETCH NEXT FROM VTPorts_Cursor INTO @PortKey, @PortDescriptor
WHILE @@FETCH_STATUS = 0
BEGIN
SET @VTLeft=1
SET @VTRight=1
WHILE(@VTLeft <= 7)
BEGIN
WHILE(@VTRight <= 4)
BEGIN
SET @NextVTPortID = dbo.fn_getNextVTPortID()
SET @VTDescriptor=@PortDescriptor+' VT '+(CAST(@VTLeft AS varchar))+'-'+(CAST(@VTRight AS varchar))
INSERT INTO gcVTPorts VALUES (
@NextVTPortID, --VTPortID int primary key
@PortKey, --SMGUID uniqueidentifier not null
@VTDescriptor, --VTDescriptor varchar 50 not null
0, --MemberCount int not null
NULL, --AllMembersTGID int null
NULL --StartingMemberNumber int null
)
SET @VTRight=@VTRight+1
END
SET @VTLeft=@VTLeft+1
END
FETCH NEXT FROM VTPorts_Cursor INTO @PortKey, @PortDescriptor
END
CLOSE VTPorts_Cursor
DEALLOCATE VTPorts_Cursor
GO
November 17, 2004 at 12:48 am
Hi Marshall,
try moving the assignment of @VTRight inside the @VTLeft's WHILE. Shouldn't the initialization of the loop counter be placed before each loop's WHILE ? As it is now, the first time the inner loop reaches @VTRight 4, it never starts from 1 for the next value odf @VTLeft.
So now, it is:
SET @VTLeft=1
SET @VTRight=1 -- THIS STATEMENT SHOULD ME MOVED
WHILE(@VTLeft <= 7)
BEGIN
WHILE(@VTRight <= 4)
BEGIN
....
and I think it should be
SET @VTLeft=1
WHILE(@VTLeft <= 7)
BEGIN
SET @VTRight=1 -- THIS STATEMENT MOVED
WHILE(@VTRight <= 4)
BEGIN
....
HTH.
Regards,
Goce.
November 18, 2004 at 8:42 am
Thanks!
That did the trick...
MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply