Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: Looping through a list of values

    Hi all


    How do i loop through a list of values returned by a select statement using TSQL. maybe using a while statement.

    cheers
    james

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Looping through a list of values

    In T-SQL? Even heart of a cursor?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Aug 2003
    Posts
    111
    The only way that i could think of doing this is perhaps to use the 'IN' keyword within the 'WHERE' CLAUSE

  4. #4
    Join Date
    Aug 2003
    Posts
    111

    Re: Looping through a list of values

    Originally posted by DoktorBlue
    In T-SQL? Even heart of a cursor?

    i'll have to look that up tonight. Never used cursors before.

    thanx dok

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Looping through a list of values

    With a cursor, you can loop through a recordset, where the cursor references to one record at a time. The best way of understanding is to look for an example.

    See DECLARE CURSOR for the syntax and soem examples in SQL Server 2000. Version 7 is similar.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Sep 2003
    Location
    Penang, Malaysia
    Posts
    9

    Re: Looping through a list of values

    Originally posted by nano_electronix
    Hi all


    How do i loop through a list of values returned by a select statement using TSQL. maybe using a while statement.

    cheers
    james
    Yep.. the only way to do a while loop is by using cursor.. If there is another much better way, I'd like to know too..

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

    Re: Looping through a list of values

    Just save result of query to temp table with IDENTITY column and make loop by this field...

    Good luck

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    give us more details, maybe there is no need to do the loop at all.

  9. #9
    Join Date
    Aug 2003
    Posts
    111
    Hi all

    I did it... woohoo, thanx for all your help guys.
    This is probably the longest TSQL i've ever written, 200+ lines and most importantly it works.

    Special thanx to dok for pointing out CURSOR as a looping solution, it works magics.

    Cheers
    James

    PS: Here is the single store procedure implementing the application logic i intended. (just to show off) hahahahah


    CREATE PROCEDURE GenerateTimesheets
    (
    @JobID decimal
    )
    AS
    BEGIN
    -- Constants
    DECLARE @AllocatedTime decimal

    -- Used for looping through recordset with CURSORs
    DECLARE @AccumulatedTime decimal -- Accumulated time spent on a particular job
    DECLARE @SpentTime decimal -- Time spent on each subtask of a job
    DECLARE @TimesheetDate datetime
    DECLARE @JobUpdateID decimal
    DECLARE @TimesheetID decimal
    DECLARE @TimesheetType int

    -- Initialize variables
    SELECT @AllocatedTime = allocatedTime
    FROM Job WHERE jobID = @JobID
    SET @AccumulatedTime = 0
    SET @TimesheetType = 1

    -- Define CURSOR for each distinct day
    -- Because timesheets are generated on a daily basis.
    DECLARE date_cursor CURSOR FOR
    SELECT DISTINCT CAST( CONVERT(varchar(10), dateSubmitted, 120) as datetime)
    FROM jobUpdate WHERE jobUpdate.jobID = @JobID

    OPEN date_cursor
    FETCH NEXT FROM date_cursor
    INTO @TimesheetDate
    -- Create timesheets for each day
    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Create a new timesheet for each day
    INSERT INTO Timesheet
    (
    timesheetDate,
    jobID,
    timesheetType
    )
    VALUES
    (
    @TimesheetDate,
    @JobID,
    @TimesheetType
    )
    -- Return the new timesheetID for reference
    SELECT @TimesheetID = timesheetID
    FROM Timesheet
    WHERE timesheetDate = @TimesheetDate AND jobID = @JobID AND timesheetType = @TimesheetType

    -- Create new cursor to loop through jobupdates for a particular day
    DECLARE jobupdate_cursor CURSOR FOR
    SELECT spentTime, jobUpdateID
    FROM jobUpdate
    WHERE @TimesheetDate = CAST(CONVERT(varchar(10), dateSubmitted, 120) as datetime)
    AND jobID = @JobID

    OPEN jobupdate_cursor
    FETCH NEXT FROM jobupdate_cursor
    INTO @SpentTime, @JobUpdateID

    -- Loop through and process all jobupdates for the day
    -- creating new timesheets for CT and TA if necessary
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @AccumulatedTime = @AccumulatedTime + @SpentTime

    ---------------------------------------
    -- if currently in standard time phase
    ---------------------------------------
    if @TimesheetType = 1
    BEGIN
    IF @AccumulatedTime >= @AllocatedTime + 2
    BEGIN
    -- Create new timesheet approval timesheet
    SET @TimesheetType = 3
    INSERT INTO Timesheet
    (
    timesheetDate,
    jobID,
    timesheetType
    )
    VALUES
    (
    @TimesheetDate,
    @JobID,
    @TimesheetType
    )
    SELECT @TimesheetID = timesheetID
    FROM Timesheet
    WHERE timesheetDate = @TimesheetDate AND jobID = @JobID AND timesheetType = @TimesheetType
    -- Update the current job with timesheet reference id

    END
    ELSE IF @AccumulatedTime >= @AllocatedTime
    BEGIN
    -- Create new completion time timesheet
    SET @TimesheetType = 2
    INSERT INTO Timesheet
    (
    timesheetDate,
    jobID,
    timesheetType
    )
    VALUES
    (
    @TimesheetDate,
    @JobID,
    @TimesheetType
    )
    SELECT @TimesheetID = timesheetID
    FROM Timesheet
    WHERE timesheetDate = @TimesheetDate AND jobID = @JobID AND timesheetType = @TimesheetType

    -- Update the current job with timesheet reference id
    UPDATE JobUpdate SET timesheetID = @TimesheetID
    WHERE jobupdateID = @JobUpdateID
    END
    ELSE
    BEGIN
    -- Update the current job with timesheet reference id
    UPDATE JobUpdate SET timesheetID = @TimesheetID
    WHERE jobupdateID = @JobUpdateID
    END
    END

    -----------------------------------------
    -- if currently in completion time phase
    -----------------------------------------
    ELSE IF @TimesheetType = 2
    BEGIN
    IF @AccumulatedTime >= @AllocatedTime + 2
    BEGIN
    -- Create new timesheet approval timesheet
    SET @TimesheetType = 3
    INSERT INTO Timesheet
    (
    timesheetDate,
    jobID,
    timesheetType
    )
    VALUES
    (
    @TimesheetDate,
    @JobID,
    @TimesheetType
    )
    SELECT @TimesheetID = timesheetID
    FROM Timesheet
    WHERE timesheetDate = @TimesheetDate AND jobID = @JobID AND timesheetType = @TimesheetType
    -- Update the current job with timesheet reference id
    UPDATE JobUpdate SET timesheetID = @TimesheetID
    WHERE jobupdateID = @JobUpdateID
    END
    ELSE
    BEGIN
    -- Update the current job with timesheet reference id
    UPDATE JobUpdate SET timesheetID = @TimesheetID
    WHERE jobupdateID = @JobUpdateID
    END
    END

    --------------------------------------------
    -- if currently in timesheet approval phase
    --------------------------------------------
    ELSE IF @TimesheetType = 3
    BEGIN
    -- Update the current job with the timesheet refernce id
    UPDATE JobUpdate SET timesheetID = @TimesheetID
    WHERE jobupdateID = @JobUpdateID
    END

    FETCH NEXT FROM jobupdate_cursor
    INTO @SpentTime, @JobUpdateID


    END
    CLOSE jobupdate_cursor
    DEALLOCATE jobupdate_cursor

    FETCH NEXT FROM date_cursor
    INTO @TimesheetDate

    END
    CLOSE date_cursor
    DEALLOCATE date_cursor

    END

  10. #10
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Hi James, very impressive! You are a fast learner.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  11. #11
    Join Date
    Aug 2003
    Posts
    111
    Originally posted by DoktorBlue
    Hi James, very impressive! You are a fast learner.
    Thanx dok !!!
    I was wondering if you also know stuffs on Oracle as I will be working with oracle very soon, next monday actually. I guess I will asking questions in the Oracle forum, but it would be great if you are a Oracle expert too.

    You have been very helpful, thanx again.

    Cheers
    James

  12. #12
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42
    I think you have to be careful with cursors as they are not the most efficient methods available. In fact most problems can be solved without the need for cursors.

    If you provide us with what you're trying to do, maybe someone will provide you with a set based solutions instead.
    Shadow to Light

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think James is quickly-learning poor programming habits. I can't believe this code does what he thinks it is doing, there are so many oportunities for errors. Why is TimeSheetType not reset to 1 when processing starts for a new day? I can't believe you need nested cursors to do whatever it is you are trying to do.

    I can't figure out the logic from the code, and that at least is going to cause problems for whoever comes along and has to revise or debug it.

    Add my vote to rdjabarov's and Crespo-n00b's that there ought to be a better solution.

    blindman

  14. #14
    Join Date
    Aug 2003
    Posts
    39
    everyone learns from mistakes, won't take long ;-)

  15. #15
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Why so negative? James just took a step into the cursor world, and you should be glad with it. And everybody, which is claiming to know a better solution than that, what James showed us, is invited to do a proposal. But just saying, that there ought to be a better solution, is too cheap, because this is a TRUE statement in almost all cases.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

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