Custom Function in Query

[Mike]

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.

 

[Douglas J. Steele]

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

 

[Baz]

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.

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.

 

[Mike]

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

 

[Douglas J. Steele]

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.

 

[Philipp Stiefel]

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

 

[Baz]

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.

 

[Mike]

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