• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Stored proc to monitor an update

Hi Experts,

I need a stored proc which will monitor an update and if nothing happens raise an error.

For example, I set a datetime variable  (@varDate) as argument of the stored proc:
if DateColumn isn't updated in MyTable ( or @varDate >= DateColumn value ) after 30 seconds, I want to raise an error.
but if DateColumnis updated in MyTable (or @varDate < DateColumn value) before 30 seconds, I want to return the new updated datetime.

Could you tell me it is dangerous for the scalability of Sql Server 2005 ?
IE: I don't want a trigger  because I want to call the stored proc from  a webservice.

Thanks in advance for your help.
0
noulouk
Asked:
noulouk
1 Solution
 
AskeetoCommented:
I think it is something like this you have in mind.


/*
    Create test table with some test data
*/
if exists (select * from sysobjects where name = 'MyTable' and type = 'U')
  drop table MyTable
create table MyTable (DateColumn datetime)

insert into MyTable values(current_timestamp)
insert into MyTable values(dateadd(second, -10, current_timestamp))
insert into MyTable values(dateadd(second, -20, current_timestamp))
GO
/*
    Create stored procedure
*/
if exists (select * from sysobjects where name = 'usp_check_update' and type = 'P')
  drop procedure usp_check_update
GO
create procedure usp_check_update @date datetime, @timeoutinseconds int = 30
as

declare @start_time datetime
select @start_time = current_timestamp
while dateadd(second, @timeoutinseconds, @start_time) > current_timestamp
  begin
   
    waitfor delay '00:00:02'

    if @date <= (select max(DateColumn) from MyTable with (nolock))
      begin
        select max(DateColumn) from MyTable with (nolock)
        return  (0)
      end
   
  end

if @date > (select max(DateColumn) from MyTable with (nolock))
  begin
    raiserror('omg. there are problems', 16, 1)
    return (1)
  end
else
  begin
    select max(DateColumn) from MyTable with (nolock)
    return (0)    
  end
GO
/*
    test procedure
*/
declare @date datetime
select @date = current_timestamp
exec usp_check_update @date
0
 
nouloukAuthor Commented:
Perfect.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now