SQL – How to Update Records in a Table Using a Loop and Cursor

I had a challenge yesterday that I would normally solve using some Visual Basic code but had to do it in pure SQL on a SQL 2005 server.

THE CHALLENGE:
Loop through all the records in a table and then update the table based on some logic or condition.

THE SOLUTION:

Using a SQL Cursor, I was able to loop through all the records in a table and then run an SQL update command for certain records that matched a particular criteria.

SQL Sample Code

DECLARE @myEmpID int

DECLARE MyCursor CURSOR FOR

SELECT DISTINCT  EmployeeID FROM Employees WHERE Company=64


OPEN myCursor


--Read the initial emploee id value from the cursor

FETCH NEXT FROM myCursor

INTO @myEmpID


WHILE @@FETCH_STATUS = 0


BEGIN

--Update goes here 

UPDATE Employees 

SET [Status] = 'T'


WHERE EmployeeID = @myEmpID AND Company=54


FETCH NEXT FROM MyCursor

INTO @myEmpID


END

CLOSE MyCursor

VBScript – Adding Days and Months to a Date Using the DateAdd Function

VBScript has a built in function called “DateAdd” that specifically deals with adding or subtracting when dealing with a date/time variable.

To use it you will need to know the syntax and parameters that it needs to make the magic happen.

For my example I’m going to get the current date and time of the system by calling the “Now()” function which grabs the date and time from the PC the script is being run on.

To add month to today’s date:

DateAdd("m",3, Now())

Since I wrote this code in March it should return a numeric “6” for June.  To spell out the month you would have to due some more work and maybe I’ll cover that in another post later.  The important thing to remember when working with dates and times using DateAdd, all your output will be numeric.

Calling the DateAdd Function in VBScript

The VBScript function, DateAdd, needs a couple of parameters separated by commas to make it work.

1. DateAdd needs to know the “Interval” parameter which is the part of the date your are wanting to work with.  In the sample above I used “m” to tell it that I want to add to the month. 

HINT: Don’t forget to wrap interval parameter text in double quotes.

2. DateAdd needs the number you want to add to the date and in my sample I used “3”. If you want to get a previous month then simply give DateAdd a negative number.

3. DateAdd needs to know what date you are working with. In my sample I used Now() to the current system date/time. You could have passed the date via a variable.

Where I had used the string value “m” to add to the month, you only have to change to interval parameter to change the year, month, day, hour, minute or second.  Here is a list of all the interval parameters you can pass to the DateAdd function:

  • yyyy – Year
  • q – Quarter
  • m – Month
  • y – Day of year
  • d – Day
  • w – Weekday
  • ww – Week of year
  • h – Hour
  • n – Minute
  • s – Second

I know for some people the DateAdd function is like preschool but for people who are new to VBScript I know it helps a lot if someone stops to explain the how and why of how this stuff works.

Good luck and happy coding!
Rick