asked
tsql sql function for a cron expression
**Q**: I need a *function* thats takes the following parameters 1. a *cron expression* 2. a datetime for *last run*(or a table with latest runs) and *returns* true or false, if to run or not. **Why**: I want to store the schedule expression in a table with *jobs* and only use **one** *SQL schedule job* to handle witch jobs should be started. I don't want to create a tally table with start times, like in link below. **Otherwise**: Add **one** *SQL schedule job* for each job I add in job table. **Links** link:[ncrontab](
https://code.google.com/p/ncrontab/wiki/SqlServerCrontab/ "ncrontab") **Tags** cron, cron expression, function, sqlserver, scheduling
Thanks for the challenge. I'm going to play around with this over the weekend.
I didn't have as much time to play with this over the weekend as I hoped I would, but I got a decent start. The following procedure will convert a cron expression into a single row table variable with 7 columns for the 7 fields in the expression. I haven't added error handling, and the real work is then checking the values in those fields to determine the schedule. I still plan to tackle this just for the heck of it, but it might be a little while until I'm done (and the solution might be a bit long). I used Jeff Moden's function for splitting the string. You can find the article [here][1] with the zip file of function scripts [here][2]. CREATE PROCEDURE Q117285.CreateJobScheduleFromCronExpression @CronExpression varchar(200) , @FieldDelimiterCharacter char(1) -- any whitespace character AS BEGIN; DECLARE @CronExpressionArray TABLE ( Seconds varchar(50) , Minutes varchar(50) , Hours varchar(50) , DayOfMonth varchar(50) , Month varchar(50) , DayOfWeek varchar(50) , Year varchar(50) ); INSERT @CronExpressionArray SELECT [Seconds] = [1] , [Minutes] = [2] , [Hours] = [3] , [DayOfMonth] = [4] , [Month] = [5] , [DayOfWeek] = [6] , [Year] = [7] FROM ( SELECT ds.ItemNumber, ds.Item FROM master.dbo.DelimitedSplit8K(@CronExpression, @FieldDelimiterCharacter) ds ) pSrc PIVOT ( MAX(pSrc.Item) FOR pSrc.ItemNumber IN ([1], [2], [3], [4], [5], [6], [7]) ) pvt ; SELECT * FROM @CronExpressionArray; END; [1]:
http://www.sqlservercentral.com/articles/Tally+Table/72993/ [2]:
http://www.sqlservercentral.com/Files/The%20New%20Splitter%20Functions.zip/9510.zip
Thanks Tom! Seams like a good start, to break the expression into a simple and understandable structure, I'll will try it, and test against a last run. /Jonas
15 People are following this question.
