Custom Function in Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 27 Sep 2005 11:11 GMT

I am converting a mdb to an adp with sql server 2000 backend. I have a
query in the mdb which uses a custom vba function in an expression
field to work out the number of working days between two dates. This
function also uses a table of Bank Holiday dates and a bit of ado to
take Bank Holiday dates out if they fall within the period.

I have figured out how to create a SQL Server Function to work out the
weekdays between two dates fed in as arguments which I can then use in
a View or Stored Procedure. However, I am stumped as to how to
implement the bit about taking out the bank holidays as well, can
anyone help? I appreciate I could hard code the dates into the SQL
Server Function but I would prefer to use a table to store the dates so
that the user can add or change dates as required.

Reply to this Message

Douglas J. Steele - 27 Sep 2005 14:11 GMT

Can you not modify the SQL Server function you've created to read from a SQL
Server table?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I am converting a mdb to an adp with sql server 2000 backend. I have a
> query in the mdb which uses a custom vba function in an expression
[quoted text clipped - 9 lines]
> Server Function but I would prefer to use a table to store the dates so
> that the user can add or change dates as required.

Reply to this Message

Mike - 27 Sep 2005 22:00 GMT

I thought you could not do this in a function. If I try putting a
SELECT statement in the function I get an error.

> Can you not modify the SQL Server function you've created to read from a SQL
> Server table?
[quoted text clipped - 17 lines]
> > Server Function but I would prefer to use a table to store the dates so
> > that the user can add or change dates as required.

Reply to this Message

Douglas J. Steele - 27 Sep 2005 23:40 GMT

To be honest, I've never created functions in SQL Server, but I was under
the impression that you could use cursors and the like, just as in Stored
Procedures.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I thought you could not do this in a function. If I try putting a
> SELECT statement in the function I get an error.
[quoted text clipped - 21 lines]
>> > Server Function but I would prefer to use a table to store the dates so
>> > that the user can add or change dates as required.

Reply to this Message

Baz - 28 Sep 2005 08:58 GMT

> To be honest, I've never created functions in SQL Server, but I was under
> the impression that you could use cursors and the like, just as in Stored
> Procedures.

Up to a point, but it will not let you use non-deterministic functions
within your own functions, which may be what is causing his error. Or
possibly not, since he doesn't say what the error actually is.

Reply to this Message

Philipp Stiefel - 28 Sep 2005 08:55 GMT

> I thought you could not do this in a function. If I try putting a
> SELECT statement in the function I get an error.

You can not just execute a plain SELECT-Statement to create a
resultset within a function as you can within a stored procedure.

However you can use a SELECT-Satement to assign a value to a
variable and use that variable as return-value of the function.

simple sample:

CREATE FUNCTION dbo.GetFooCount (
    @criteria varchar(100)
)
    RETURNS int
AS
BEGIN

    DECLARE @retVal int

    SELECT @retVal = COUNT(*)
    FROM dbo.tblFoo
    WHERE Something = @criteria

    RETURN @retVal

END
GO

You may also create a table-function that does return a a table
instead of a scalar value. The details of this approach are
beyond the scope of what I've time to explain, so please refer to
the documentation for durther details.

Cheers
Phil

Reply to this Message

Mike - 30 Sep 2005 09:57 GMT

Thanks, this was the solution, I was just not familiar with what you
could do in SQL Server

Reply to this Message

Baz - 27 Sep 2005 15:58 GMT

> I am converting a mdb to an adp with sql server 2000 backend. I have a
> query in the mdb which uses a custom vba function in an expression
[quoted text clipped - 9 lines]
> Server Function but I would prefer to use a table to store the dates so
> that the user can add or change dates as required.

You could create a table that contains a row for EVERY day for, say, the
next 50 years. Only about 18000 rows, not a problem. It would have two
columns:

the_date (primary key)
workday (bit)

It would be pretty easy to create a query which, for every date in the
table, sets the value of workday according to whether it's a weekday. Then,
all you need to do is to manually set the bank holidays for as far ahead as
you can/want to. Having done that, counting the working days between two
dates becomes a simple aggregate query.

Or, you could make it a bit smaller by making it a non-working-day table,
containing just Sats and Suns (pre-loaded) and bank holidays. The counting
queries would still be pretty simple.

Reply to this Message