Create Event with variable schedule-spec

[Baron]

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!!

[VolkerBarth · Accepted solution]

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

[Baron · Accepted solution]

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;

[VolkerBarth · Accepted solution]

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

[Baron · Accepted solution]

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

Thank you

[VolkerBarth · Accepted solution]

So does it work now?

[Baron · Accepted solution]

Yes, it worked!