Apply an sql function to update an sql table

[MJV57]

I have a table with the followin columns:
(StartDate, EndDate and BusinessDays)
The BusinessDays colums is to be calculated using the scalar function called GetBusinessDays; however Im relatively green at sql programming and not sure how to call these the tables fiels in a procedure and apply the GetBusiness Days function to insert the calculated BusinessDays in the BusinessDays column of my table. Any help would be appreciated as i have tried but failed.
My GetBusinessDays function looks as follows:

USE [ProjCoorTools]
GO
/****** Object: UserDefinedFunction [dbo].[GetBusinessDays] Script Date: 12/10/2009 09:20:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[GetBusinessDays]
(
@StartDate datetime,
@EndDate datetime
)
returns int
as
begin

declare @DaysBetween int
declare @BusinessDays int
declare @Cnt int
declare @EvalDate datetime

select @DaysBetween = 0
select @BusinessDays = 0
select @Cnt=0

select @DaysBetween = datediff(Day,@StartDate,@endDate) + 1

while @Cnt < @DaysBetween
begin

select @EvalDate = @StartDate + @Cnt

if (dbo.IsDateAHoliday(@EvalDate) = 0)
BEGIN
if ((datepart(dw,@EvalDate) <> 1) and (datepart(dw,@EvalDate) <> 7))
BEGIN
select @BusinessDays = @BusinessDays + 1
END
END

select @Cnt = @Cnt + 1
end

return @BusinessDays
end

 

[gmmastros]

First, verify that this returns the correct data.

[tt][blue]
Select *, dbo.GetBusinessDays(StartDate, EndDate)
From YourTableName
[/blue][/tt]

If that works, then you can update the table like this.

Update YourTableName
Set    BusinessDate = dbo.GetBusinessDays(StartDate, EndDate)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

If this is something that needs to happen everytime a row is inserted, you might want to consider making BusinessDays a COMPUTED COLUMN.

ALTER TABLE <tablename>
ALTER COLUMN BusinessDays AS (dbo.GetBusinessDays(StartDate, EndDate))

SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875