Complicated - updating fields in multiple rows using return values 1

[jmilne]

I work for a housing association as a trainee programmer. Currently I am writing a vb.net program which tracks the progress of our houses from the time a tenant moves out to the time the house is re-let. Behind it is an SQL Server database.

The keys of each property are passed from department 1 to department 2 to department 3 to the new tenant. When the keys are passed the DepartmentNameDatePassed field is set to the current date.

My main table includes four fields which store the number of working days the keys have been with each department: WDDep1, WDDep2, WDDep3 and WDTotal.

Every day the system must update the number of working days so far that the keys have been held by the particular department they are currently with. For example, all records for which Dep1DatePassed IS NULL need to have the WDDep1 field updated.

I have written a stored procedure which takes a datetime value as a parameter and returns the exact no. of working days between that datetime and the current datetime as an output parameter. The cut off time used is 12:00pm.

I need to be able to use this stored procedure somehow to update the four different fields in multiple rows at once.

The multiple update needs to run once every day at 12:00pm as follows:

If Dep1DatePassed IS NULL then UPDATE WDDep1.
If Dep2DatePassed IS NULL then UPDATE WDDep2.
If Dep3DatePassed IS NULL then UPDATE WDDep3.
If DateTenantMovedIn IS NULL then UPDATE WDTotal.

I have not been able to find any documented help explaining how to use a return value from a stored procedure in an UPDATE command.

Any help/tips/SQL examples would be greatly appreciated.

Many thanks
Jean

 

[SQLSister]

Are you using a SQL2000 database? If so, I would rework your stored procedure into a User-defined function. Functions can be used in update statements just like variables or field names. So suppose your function is called ufn_CalcWorkingDays and has one parameter of date, to use it in an update then do the following

Update Maintable
Set WDDep1 = ufn_CalcWorkingDays(dt.Dept1DateReceived)
From DetailTable dt
Where dt.Dep1DatePassed IS NULL

If you need to use the stored procedure, you can insert the results into a table variable or temp table and use just like any other table to do the update. But if your stored procedure only calcutes one record at a time, this will be time consuming, you will need a cursor to run through all the dates, and you are much better off using the function which will do only one update statment per department as the solution I gave you is set-based, not record-based.

 

[jmilne]

Thanks for replying...funnily enough, straight after I posted I found several articles about user defined functions and have subsequently already converted the stored procedure to a function. It didn't take long after that to write a second stored procedure which calls the function and then set it up to execute as a scheduled job every day at 12:00pm. Thanks again for replying as it confirms that I did the right thing.