auto updating a table field

[engisa]

Hi all,

I wanted to execute the query stored in a table with given intervals.

I have a table called "calculations". it has the following structure:

id | period | sql | result |

now, in the sql field, I store the sql clause to be executed. In the period field, there is time interval for the query to run. Result field stores the result of the sql clause.

Example:

1 | 5 | select count(*) from mytable | 1

this means that every 5 minutes, take the given query, execute it, and update the result field.

I could not find a way to do this.

Any idea?

Thanks...

[sknake]

This is a bad idea, by the way, but to answer your question you could do this:

IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table
Create Table #Table
(
  id int,
  period int,
  [sql] varchar(7000),
  result int
)
GO

Insert Into #Table (id, period, sql) values (1, 5, 'select count(*) from #table')
Insert Into #Table (id, period, sql) values (1, 6, 'select count(*) from #table')

GO

Declare @id int, @sql varchar(7000)

DECLARE det_cursor CURSOR FOR
Select  id, sql
From #Table

OPEN det_cursor

FETCH NEXT FROM det_cursor INTO @id, @sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
  exec('Update #Table Set result = (' + @sql + ') where id = ' + @id)
  FETCH NEXT FROM det_cursor INTO @id, @sql
END

CLOSE det_cursor
DEALLOCATE det_cursor

GO

Select *
From #Table

That of course assumes you will have a "standard" query in the sql column. Beyond that you should probably use an application for these tasks.

[padtes]

I think above anser is missing one point:

In the period field, there is time interval for the query to run.

Assuming certain limitations, given below, other solution is to write stored procedure (as given above) and schedule it as a SQL server job.

Limitation 1: you need to know how frequently the job will execute (like every 5 min or 10 min). Though this depends on the data in the table, you can come up with a minimum, such as 5 min, keep an additional column in the table as "execute next time" and SP will look all into this column executing all queries one by one that are past, update this time for next execution

Limitation 2: update column must be same for all queries. If the result is sometimes date-time and sometimes numeric percision, say 4, then there is possiblity of mess. For ex. one query is add 3 days from today select dateadd(d, getDate(), 3) and other query is increase result by 10% select result * 1.1 from calculations .
Of course you can use varchar and converts in the queries.

Advantage: Writing SP is simple. It is already given above.

[engisa]

Thanks to both of you.

Your help is very appreciated.

I'm working on it now...