Complex - translate fn and stored proc into an MDB

[Hfnet]

Bear with me on this!

I have a function and a stored procedure in an ADP which I want to use in an MDB linking to SQL (plus Oracle)

The function calculates working days, and the stored procedure will create a number depending on working days in and table fields which will colour a text box according to how late the job is. This is the function:

ALTER FUNCTION dbo.fn_calcworkdays
(@dateStart as datetime, @dateEnd as datetime, @dateToday as datetime)
RETURNS INT
AS
BEGIN
DECLARE
@dateEn datetime,
@dateCount int,
@weekDay int,
@x int,
@y int,
@z int,
@result int

IF @dateEnd IS NULL SET @dateEnd=@dateToday
IF (@dateStart Is NULL) Or (@dateStart>@dateEnd)
BEGIN
RETURN 0
END


SET @dateCount=datediff(d,@dateStart,@dateEnd)+1
SET @weekDay=datepart(dw,@dateEnd)
SET @x=5*Round(@dateCount/7,0)
SET @y=@dateCount % 7

IF @y=0
SET @z=0
ELSE BEGIN
SET @z=@y-@weekDay
IF @z>2 SET @z=2
IF @z<0 SET @z=0

IF @weekDay=6 SET @z=@z+1
IF @weekDay=7
BEGIN
SET @z=@z+1
IF @y>1 SET @z=@z+1
END
END

SET @result=@x + @y - @z

RETURN @result
END

Which i need to translate somehow to use in a query in my MDB.

The code I use in the stored procedure is:

CASE WHEN dbo.ALItemsList.ALItemRepairStagesID <= 4 THEN CASE WHEN dbo.fn_calcworkdays(dbo.ALItemsList.DateIn, dbo.ALItemsList.DateGone,
{ fn NOW() }) <= dbo.ALCustomerCategories.CustCatToBeDone THEN 1 WHEN dbo.fn_calcworkdays(dbo.ALItemsList.DateIn, dbo.ALItemsList.DateGone,
{ fn NOW() }) <= dbo.ALCustomerCategories.CustCatLate THEN 2 WHEN dbo.fn_calcworkdays(dbo.ALItemsList.DateIn, dbo.ALItemsList.DateGone,
{ fn NOW() })
> dbo.ALCustomerCategories.CustCatLate THEN 3 END

Which I need to translate into a query in my MDB

Any help greatly appreciated!!!

 

[cmmrfrds]

In a pass-thru query you can write native Transact_SQL code. That way you should be able to use the function that is in sql server. Will that work for you.

 

[Hfnet]

nope, it didn't. All I really want to do is to get the working days from a table in a query in an MDB...

 

[lespaul]

the pass through query didn't work? or the function didn't work in the pass through query? or the query didn't return the correct information?

What exactly did you try that failed to work?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Hfnet]

The function didn't work.

I tried creating the function in SQL Server, which went ok. the pass-through query failed with an error contacting SQL or unknown procedure type of error.

 

[fredericofonseca]

are you saying that calling your existing stored procedure failed when executed from a pass-through query? if so what error did you receive? and did you try and do any type of testing with a simple sql instead of your SP?.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com