Question Re: Create Event with variable schedule-spec in Technology Q&A

Create Event with variable schedule-spec

Baron — Tue, 24 Sep 2019 07:26:29 GMT

Hello, is there any way to use a variable (of type time) in the statement of creating an event?

begin

set @TIME = '09:00';

create event myevent schedule start time @TIME every 5 minutes handler begin end;

end

I tried even with execute immediate without success!!

Re: Create Event with variable schedule-spec

VolkerBarth — Tue, 24 Sep 2019 08:14:01 GMT

This should work with EXECUTE IMMEDIATE when specifying the variable as a literal value, AFAIK. What exactly have you tried?

Re: Create Event with variable schedule-spec

Baron — Wed, 25 Sep 2019 08:52:33 GMT

I am trying to execute this block:

begin

declare @time1 time;

set @time1 = '09:00';

execute immediate ('create event SyncPrAtH schedule start time ''' + convert(varchar(5), @time1) + ''' every 3 minutes handler');

begin

select * from dummy;

end;

end;

Re: Create Event with variable schedule-spec

VolkerBarth — Wed, 25 Sep 2019 09:16:11 GMT

You have to include the whole CREATE EVENT statement including the handler block as argument to EXECUTE IMMEDIATE.

Re: Create Event with variable schedule-spec

Baron — Wed, 25 Sep 2019 10:01:07 GMT

Oh, Sorry!! I dont know why I was so fast to post it here before looking more closely into my code.

Thank you

Re: Create Event with variable schedule-spec

VolkerBarth — Wed, 25 Sep 2019 10:37:04 GMT

So does it work now?

Re: Create Event with variable schedule-spec

Baron — Wed, 25 Sep 2019 15:58:42 GMT

Yes, it worked!