Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Posts
    23

    Question Unanswered: Loop through records?

    I am new to stored proc.

    For eg, in ASP or VB6, we usually use a Do While ...rs.MoveNext...Loop to go through all the records in a table.

    What is the equivalent of doing it in stored procs?

    Please advise.

    Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Loop through records?

    Of course you can use cursor but it is much better to use rowset statemets - it depens on what do you want to do with your data.

  3. #3
    Join Date
    May 2003
    Posts
    23
    Thanks snail.

    I just figured out cursors through trial and error by reading the books online.

    i also just finished reading thru news groups.

    many says that cursors are harmful and hogs down system resources? because this script will be run once a user logs into the system. what will be the alternative to cursors? there is an alternative way which i heard is to actually pass some data into temp tables?

    pls advise.

    here are the scripts i wrote:

    Code:
    CREATE procedure usr_clearworkerdp as
    declare @wid varchar(10)
    declare workercursor cursor for 
    select worker_id from worker where datediff(year, date_of_clearance,getdate() ) = 0 or datediff(year, date_of_clearance,getdate() ) >0
    open workercursor
    fetch next from workercursor into @wid
    while @@FETCH_STATUS = 0
    begin
    update violations_committed set dp = '0' where worker_id = @wid
    fetch next from workercursor into @wid
    end
    close workercursor
    deallocate workercursor
    GO

  4. #4
    Join Date
    Sep 2003
    Location
    Portland, Oregon
    Posts
    11
    If your update is as simple as the code you wrote, can't you just do this?

    Code:
    UPDATE worker
    SET dp = '0'
    WHERE DATEDIFF(year, date_of_clearance, GETDATE()) >= 0
    There is nothing about your update that would require doing anything one row at a time.

    Rob

  5. #5
    Join Date
    May 2003
    Posts
    23
    Originally posted by rgarrison
    If your update is as simple as the code you wrote, can't you just do this?

    Code:
    UPDATE worker
    SET dp = '0'
    WHERE DATEDIFF(year, date_of_clearance, GETDATE()) >= 0
    There is nothing about your update that would require doing anything one row at a time.

    Rob
    the date_of_clearance belongs to the worker table. the logic is to check the dates, then if more than a year, update dp column in the violations_committed table.

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Post

    ur update changes to : -

    update b
    set b.dp = 0
    from worker a,violations_committed b
    where DATEDIFF(year, a.date_of_clearance, GETDATE()) >= 0
    and a.worker_id = b.worker_id


Posting Permissions

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