Recurrence for Calendar using MS SQL Server
Posted on 2009-02-14
I need help =) I'm very, very, very new to Sql (and very, very, very confused),
so forgive me if I don't say things properly.
I have a calendar of events and I want to be able to allow a user to input recurring events.
I've been hunting around to try to figure out the best method to solve this problem.
From searching around on this site, I found some soltions (to different problems)
that used User Defined Functions, so I thought maybe this would be the route that I should take.
My simplified database schema is as follows:
(table Name = dbo.Events)
The Recurrence has the following values
Reccurence = 0 : No reccurence
Reccurence = 1: Event Reccurs on a weekly basis
Reccurence = 2: Event Reccurs on a monthly basis
Reccurence = 3: Event Reccurs on a yearly basis
I need to be able to retrieve all of the events for a customer. If the event reccurence is 0, then it's
just a normal retrieval, but if it anything else, then I need to populate a table and join it to the
other results, which is why I thought that User Defined Functions would be a good solution.
After fighting with UDFs for the past 3 hours, I ran into soooo many problems.
1) Is it possible to create UDFs from MS Visual Studio 2005? I ask this becasue I couldn't even get the scalar UDF to work.
2) None of the example that I found showed how to pass in a variable from outside the function...kind of like a stored procedure.
what I mean is this
fEvent(@CustomerID) is the parameter for the UDF
when I call the function in the SELECT STATEMENT
SELECT CustomerID, EventTitle, BeginningDate, EndingDate
FROM dbo.Events E
CROSS APPLY fEvent(E.CustomerID)
ORDER BY BeginningDate
What if I don't want to hardcode in the E.CustomerID. It's dependent on which Customer I looking up.
How can I do this?
3) CROSS comes up as a keyword for me but APPLY doesn't. Any thougths as to why that is? I'm using Visual Studio 2005 Professional
and MS Sql Server Express
4) Is this the best method to achieving my goal? The only other was that I thought of was to have
a seperate entry for each occurence, but this would generate tons of records, and produce a lot of hits
on my database.
Thank you so much any help!!!!