SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Looping Through Records in Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

delta1186
Starting Member

USA
10 Posts

Posted - 10/30/2007 :  13:28:43  Show Profile  Reply with Quote
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

Edited by - delta1186 on 10/30/2007 13:30:59

mangla.mohit
Starting Member

India
1 Posts

Posted - 10/30/2007 :  13:55:46  Show Profile  Reply with Quote
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
Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts

Posted - 10/30/2007 :  14:12:43  Show Profile  Reply with Quote
Read Books OnLine on the subject of cursors.

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/30/2007 :  14:30:15  Show Profile  Reply with Quote
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
Go to Top of Page

delta1186
Starting Member

USA
10 Posts

Posted - 10/30/2007 :  17:30:33  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000