Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: for loop

  1. #1
    Join Date
    May 2003
    Posts
    40

    Unanswered: for loop

    HeaderLoop: for forHeader as curHeader dynamic scroll cursor for
    select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 do
    set AcctNum=lngALSHeadrID;
    exec "DBA".sp_ALSHeadr2Policy(AcctNum);
    set Cntr=Cntr+1
    end for;


    The above is the sybase version of a 'for loop' . The query
    select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 results in 1000 results.
    How do I change that in SQL?? Do we have a for loop ??
    I checked in BOL but it is confusing with "browse" etc n some other options.

    can I write like this?


    for { Browse { declare curHeader dynamic cursor for
    select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 }
    set @AcctNum=lngALSHeadrID;
    exec "DBA".sp_ALSHeadr2Policy(@AcctNum);
    set @Cntr=@Cntr+1
    }


    I duno its just my guess, can any one help me out. @Cntr and @Acctnum are declared
    in the beginnning.



    tks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    DECLARE @cntr int
    SELECT @cntr=0
    WHILE @cntr < 1000
      BEGIN
    	SELECT @cntr = @cntr + 1
    --	Other statements
      END
    SELECT @cntr
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2003
    Posts
    40
    Brett

    thanks for the help, look at wut I did. first thing it is not sure that
    it is 1000 records. So I counted total num of records and did looping
    with that count. But the problem is at the select stmt (in the loop)
    the stmt gives 1000 (assume) records, thus 1000 values to the
    the variable lngAlsHeadrID, where in the loop we are taking each
    value and then setting it to the variable @AcctNum. Hope u got my ques.
    My answer should be like this, I should take each value of
    lngAlsHeadrID set it to @Acctnum and then pass it thru sproc and then
    take the second value n so on.........


    set @RowCount = select count(lngALSHeadrID) from ALSHEADR where
    lngFedTaxID>0 and lngFedTaxID<999999999 }

    WHILE @cntr < @RowCount1
    BEGIN
    declare curHeader cursor dynamic for
    select lngALSHeadrID from ALSHEADR where lngFedTaxID>0
    set @AcctNum = lngAlsHeadrID
    Exec ALSHeadr2Policy(@AcctNum)
    SELECT @cntr = @cntr + 1
    END
    SELECT @cntr


    Can you suggest me??

    Thanks alot

  4. #4
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Just to be picky, I would substitute SET for SELECT in Brett's code, 'cause MS says it's better to, and I do everything they say 8-}
    -bpd

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How big is ALSHeadr2Policy?

    Can you post it?

    I'd rather give you a set based solution.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Does smell like a join waiting to happen, doesn't it?
    -bpd

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by bpdWork
    Just to be picky, I would substitute SET for SELECT in Brett's code, 'cause MS says it's better to, and I do everything they say 8-}
    I seen huge global wars about that...WHERE specifically did yo see that?

    And what would you prefer

    SET @a = 1
    SET @b = 1
    SET @c = 1
    SET @d = 1
    SET @e = 1
    SET @f = 1

    Or

    SELECT @a = 1, @b = 1, @c = 1, @d = 1, @e = 1, @f = 1

    Wouldn't you think 1 op is better than 6?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    May 2003
    Posts
    40
    Brett

    Thats too big. It has several for loops, while loops and all.
    If I can get the idea of syntax for this for loop then I can try to
    solve that sproc. It is around 500 lines, just donn wann to confuse you.

    Any more suggestions for the above ques.



    Thanks

  9. #9
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    I got it from BOL under SELECT @local variables:
    "It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable. For more information, see SET @local_variable."

    Actually, I think it ends up being 6 ops anyway. I guess it depends on whether you are in the "1 operation per line" camp or not.

    I fall somewhere in between: One declare statement, new line for each variable.
    -bpd

  10. #10
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Just out of curiousity, could you pst one of the loops from the big sp?

    -b
    -bpd

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Wait a minute, but isn't kir441 talking about a cursor? Check BOL for example of cursor declaration and usage.

  12. #12
    Join Date
    May 2003
    Posts
    40
    Rdja

    Even I got the same idea. Whether I can use a cursor instead of that loop.??

    bdp: Here is the for loop


    for forIns as curIns dynamic scroll cursor for
    select decAccountNum as AccountNum,strInsType,intTypeCode,strVendCode,
    datLoanEnds,intTerm,curPremium,strPremType,curFace Amt,strPolicyNum, datStarted,datPlaced,datCanceled,strInsID,strAutoC ancel,datAutoCanceled
    from "DBA".ALSINSUR where ALSINSUR.decAccountNum=AcctNum do
    set PolicyNum=decAccountNum||'-'||strInsType||'-'||intTypeCode;
    set Premium=curPremium;
    ----------------------------- Make sure we have a product
    set ProductID=null;
    select MIN(lngProductID) into ProductID from "dba".Products
    where strShortCode=strInsType and strVendorCode=strVendCode and lngVendorID=18; %% FTB ALS System
    if ProductID=null or ABS(0||ProductID)<1 then
    -----Add Product to Product Table (ADD LATER)
    call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','ERROR ','Need to Add'
    ||strInsType||'-'||strVendCode||' to the Products tables.')
    end if
    ;
    if ProductID=null or ABS(0||ProductID)<1 then -- Policy will not be added
    call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','ERROR ','Illegal ProductID '
    ||strInsType||'-'||strVendCode||' for PolicyNum='||PolicyNum)
    else
    -- We have a Person, Customer and Product that are setup correctly
    set PolicyID=null;
    select MIN(lngPolicyID) into PolicyID from "DBA".Policies
    where strPolicyNumber=PolicyNum and lngProductID=ProductID;
    set bForced=0;
    set PayModeID=8; -- Single Payment
    set PayMethID=3; -- Included in Loan Amout
    if strInsType='NIU' then -- Loans that premiums funded as part of the loan
    set bForced=1
    end if
    ;
    set RefEmpID=null;
    set BranchID=null;
    set AgentID=null; -- put in default AgentID of Default Record
    select MIN(lngEmployeeID) into RefEmpID from "DBA".Employees where lngFTBEmpID=lngOfficerID;
    select MIN(lngBranchID) into BranchID from "DBA".Branches where lngRespCntr=intRespCenter;
    if PolicyID=null or ABS(0||PolicyID)<1 then -- Need to add Policy
    insert into "DBA".Policies(lngBranchID,lngReferEmpID,lngCustom erID,lngPmntModeID,
    lngPmntMethID,lngProductID,strPolicyNumber,strStat us,datOrigEffective,
    datEffective,datRenewal,curPremium,lngAgentID,blnF orced) values(
    BranchID,RefEmpID,CustID,PayModeID,PayMethID,Produ ctID,PolicyNum,'IF',
    datStarted,datPlaced,datExpDate,Premium,AgentID,bF orced);
    if sqlstate<>'00000' then
    call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','ERROR ','Cannot Add Policy Record for PolicyNum='
    ||PolicyNum||' Loan # '||AcctNum) --else call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','TEST' ,'Added PolicyNum='||PolicyNum)
    end if
    else -- Update the current Policy
    update "DBA".Policies set lngBranchID=BranchID,lngReferEmpID=RefEmpID,
    lngCustomerID=CustID,lngPmntModeID=PayModeID,lngPm ntMethID=PayMethID,
    lngProductID=ProductID,strPolicyNumber=PolicyNum,s trStatus='IF',
    datOrigEffective=datStarted,datEffective=datPlaced ,datRenewal=datExpDate,
    curPremium=Premium,lngAgentID=AgentID,blnForced=bF orced,datLastStamp=current timestamp
    where lngPolicyID=PolicyID and lngProductID=ProductID --call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','TEST' ,'Updated PolicyNum='||PolicyNum||' ProductID:'||ProductID)
    end if
    end if -- ProductID = Null
    end for -- Insurances
    end if -- CustID
    end if -- PersonID
    end for; -- ALSHeadr's



    ignore those sybase commands as we can change that stuff.
    but this is one of the for loops in the sproc.


    Any suggestions.


    Tks

  13. #13
    Join Date
    Nov 2003
    Posts
    94
    declare @a int
    declare @a1 int
    declare @a2 int
    declare @a3 int
    declare @a4 int
    declare @a5 int
    declare @a6 int
    declare @cntr int

    set @cntr=0

    while @cntr < 100000
    begin
    set @a = @cntr
    set @a1 = @cntr
    set @a2 = @cntr
    set @a3 = @cntr
    set @a4 = @cntr
    set @a5 = @cntr
    set @a6 = @cntr
    set @cntr = @cntr+1
    end

    EXECUTION DURATION : 1.28 seconds

    -----

    declare @a int
    declare @a1 int
    declare @a2 int
    declare @a3 int
    declare @a4 int
    declare @a5 int
    declare @a6 int
    declare @cntr int

    set @cntr=0

    while @cntr < 100000
    begin
    select @a = @cntr, @a1 = @cntr, @a2 = @cntr, @a3 = @cntr, @a4 = @cntr, @a5 = @cntr, @a6 = @cntr, @cntr = @cntr+1
    end


    EXECUTION DURATION: 0.33 seconds

  14. #14
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Try This
    Code:
    declare @i int
    select @i =  id from sysobjects)
    select @i
    go
    declare @i int
    set @i = (select id from sysobjects)
    select @i
    go
    SELECT @local_variable is usually used to return a single value into the variable. It can return multiple values if, for example, expression is the name of a column. If the SELECT statement returns more than one value, the variable is assigned the last value returned.

    If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.


    Use the SET statement to assign a value that is not NULL to a declared variable. The SET statement that assigns a value to the variable returns a single value.

    BTW I prefer
    SELECT @local_variable
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah me too....but I confirmed that the SET execution is faster (for 1000 iterations) by about 4000 milliseconds...

    careful...don't blink....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •