Looping Through Records in Stored Procedure

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

Transact-SQL (2000)

Looping Through Records in Stored Procedure

Author

Topic

delta1186
Starting Member

10 Posts

Posted - 2007-10-30 : 13:28:43

I am sure I am not the first to ask this question and will certainly not be the last. I have included my code for the stored procedures below. The second procedure has purposely been cut short, because it is not important.

My scenerio is this. I am attempting to loop the values of one set of records and write them into another table using ImportBidPackages. I have added a comment in the second SP above the PRINT 'test' statement. Apparently the WHILE EXISTS (SELECT ...)
does not actually step through the values, but merely return a TRUE or FALSE value which if it is TRUE it will loop forever.

So my question is based on my sample, how do you loop through a set of records and then use the selected value? I hope I was clear enough in the explanation of my issue.

CREATE PROCEDURE ImportBidPackages
(
@intEstID integer,
@intBP tinyint
)
AS
INSERT INTO tblEstimateBidPackages (intBidPackageID,intEstimateID)
VALUES (@intBP,@intEstID)

GO

CREATE PROCEDURE ImportCallListFromEstimate
(
@intEstID integer,
@intImportID integer,
@blnBPs bit,
@blnBidders bit,
@blnSpecs bit,
@blnInclusions bit,
@strUsername varchar(25)
)
AS
DECLARE @intBidPackID tinyint

IF @blnBPs = 1
BEGIN
IF EXISTS (SELECT intBidPackageID FROM tblEstimateBidPackages WHERE intEstimateID = @intEstID)
WHILE EXISTS (SELECT intBidPackageID FROM tblEstimateBidPackages WHERE intEstimateID = @intEstID)
BEGIN
--the WHILE EXISTS is an infinite loop
PRINT 'test'
--EXEC ImportBidPackages
--intBidPackageID,@intImportID
End
ELSE
BEGIN
RETURN 1
END
END

mangla.mohit
Starting Member

1 Post

Posted - 2007-10-30 : 13:55:46

WHILE EXISTS (SELECT intBidPackageID FROM tblEstimateBidPackages WHERE intEstimateID = @intEstID)
BEGIN
--the WHILE EXISTS is an infinite loop
PRINT 'test'
--EXEC ImportBidPackages
--intBidPackageID,@intImportID
End

It is going in infinite loop as you are not changing the value of @intEstID inside the loop. Everytime while loop checks the same value and it goes inside the loop.
I think you forgot to change the value of @intEstId inside the loop.
Following statement needs to be added inside the loop :- set @intEstID = @intEstID +1 or you can change it based on your requirement.
The main objective here is to change the counter value inside the value.

Thanks,
Mohit Mangla

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-10-30 : 14:12:43

Read Books OnLine on the subject of cursors.

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 14:30:15

I usually do something like this:


DECLARE @intEstID int, @intRowCount int
SELECT @intEstID = -1,  -- Smaller value than any real data
       @intRowCount = 1 -- Force first iteration
WHILE @intRowCount >= 1
BEGIN
    SELECT @intEstID = MIN(intEstimateID)
    FROM   tblEstimateBidPackages
    WHERE  intEstimateID > @intEstID
    SELECT @intRowCount = @@ROWCOUNT

    IF @intRowCount >= 1
    BEGIN
        ... Process the row for intEstimateID = @intEstID ...
    END
END

You could use a cursor (and indeed if all you want to do is call an SProc with the @Parameters for a single row from a query that may work well), but to iterate around a table this sort of loop is usually better than a cursor

Kristen

delta1186
Starting Member

10 Posts

Posted - 2007-10-30 : 17:30:33

Thanks for all the suggestions. I found my solution by using cursors. I have never used them before today, but they were the obvious choice for this problem. I have included a snippet below for anyone who is interested in the solution.

Regards,
Kevin
*********************************************************
IF @blnBidders = 1
BEGIN
IF EXISTS (SELECT intBidPackageID FROM tblEstimateBidPackages WHERE intEstimateID = @intImportID)
BEGIN
DECLARE bp_cursor CURSOR FOR
SELECT intBidPackageID FROM tblEstimateBidPackages WHERE intEstimateID = @intImportID

OPEN bp_cursor

FETCH NEXT FROM bp_cursor
INTO @intBP

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ImportContactsFromEstimate
@intEstID,@intBP,@intImportID,@strUsername

FETCH NEXT FROM bp_cursor
INTO @intBP
END

CLOSE bp_cursor
DEALLOCATE bp_cursor
END
ELSE
BEGIN
RETURN 2
END
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources