Looping in SQL Server 7.0 Stored Proc

Results 1 to 3 of 3

Thread: Looping in SQL Server 7.0 Stored Proc

  1. #1
    Join Date
    Dec 1969

    Default Looping in SQL Server 7.0 Stored Proc

    A very interesting task for me. But need ur help...<BR>I need to write stored procedure that does the following:<BR><BR>1) Pull list of users from employee table ( I can write select st for this...but wait it gets interesting)<BR>2) Store this list in a container such as array or String so i can scan through that list.<BR>3) Pull one user at a time from the list and make updates in vacation table for that user (I can do this by writing update statement...but need that user from the whole list)<BR>4) Loop through the list of all the users pulled from the employee table and do the updates one by one, since this update is not like batch-update but it is specific to the user.<BR><BR>can some pl let me know how to do above steps, or atleast how to store data in the list and then extract one at a time...<BR><BR>PS: I am use SQL Server 7.0<BR>G

  2. #2
    Join Date
    Dec 1969

    Default RE: Looping in SQL Server 7.0 Stored Proc

    What about using a temporary table? Do something like:<BR><BR>Create Table TempEmpVacList as <BR>Select EmployeeID from Employees<BR>Where... and so on.<BR><BR>Then just select and delete one record at a time from that temp table as you update the vacation table.<BR><BR>Can&#039;t tell you how to do the looping part... sorry. (Is there such thing as a GOTO in SQL?)

  3. #3
    fred Guest

    Default RE: Looping in SQL Server 7.0 Stored Proc

    I´m not sure if this is what you are looking for and it is not a stored procedure, but it might give you an idea...<BR><BR><BR>&#039;Get all employes and store in local array<BR>rs.Open "SELECT empID FROM Employes",db<BR>emparr = rs.getRows()<BR>rs.Close<BR><BR>&#039;Start loop<BR><BR>For i = 0 TO Ubound(emparr,2)<BR><BR>&#039;If you need to get values from other table get them here <BR>&#039;and compare using empID = "& emparr(0,i)<BR><BR>db.Execute "UPDATE EmployeeVacation SET field = &#039;value&#039; "&_<BR>"WHERE empID = "& emparr(0,i)"<BR><BR>Next<BR><BR>Good luck!

Posting Permissions

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