cancel
Showing results for 
Search instead for 
Did you mean: 

Stored procedure with sleep

Former Member
0 Kudos

Hi experts

Do you have an idea how to creat a stored procedure which will have as input parameter time in seconds, and will sleep for this amount of time?

Thanks

Edna

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Edna,

As I know, you maybe cannot do it in stored procedure so far, normally, we hope that the SP can run more and more quickly.

If you are using application, such as ui application, you can call JavaScript function to implement it.

Why are you doing it? what situation?

Regards,

Jerry

Former Member
0 Kudos

Hi Jerry,

It is needed for testing purposes.

Eventually I implemented it by using a while do loop. Not brilliant, but it works.

Former Member
0 Kudos

Hi Edna,

You are using loop to implement it, it cannot control for sleeping time, such as you can loop 1000 times, but I think that the each time maybe different, it is based on server performance, and connection account and so on.

Anyway, so far I think that it has no good solution for your question within SP side.

Regards,

Jerry

Former Member
0 Kudos

Hi Jerry,

The following should do the trick:

BEGIN

vEND_LOOP := ADD_SECONDS(now(),:InSeconds);

vSTOP := 0;

while vSTOP = 0

     Do

     IF vEND_LOOP <= now() THEN

     vSTOP := 1;

     END IF;

END WHILE;

END

Regards,

Edna

Former Member
0 Kudos

Well this is not sleeping, this is burning cpu time...

Cheers Michael

Former Member
0 Kudos

Hi Edna,

Although you can implement it based on your script, but I think that it is not good solution, It will impact other thread or process, and reduce performance.

Anyway, if it is only test purpose, it is ok for your purpose; it maybe SAP HANA can implement it by own function or API in future, since then you can study or use it.

GOOD LUCK.

Regards,

Jerry

Former Member
0 Kudos

This message was moderated.

Former Member
0 Kudos

Hi Edna,

I am not sure if there is any 'sleep' or 'wait' function in HANA. However, if your requirement is

to make the procedure wait for a specified time(in seconds) then you can probably achieve that in the following way:

CREATE PROCEDURE PLEASE_SLEEP ( IN SLEEPING_TIME INTEGER, OUT WOKE_UP_AT TIME )

LANGUAGE SQLSCRIPT READS SQL DATA AS

V_TIME TIME;

V_TIME_TO_WAKE TIME;

BEGIN

V_TIME := CURRENT_TIME;

V_TIME_TO_WAKE < ADD_SECONDS ( TO_TIME( V_TIME ), SLEEPING_TIME );

WHILE V_TIME != V_TIME_TO_WAKE DO

V_TIME := CURRENT_TIME ;

END WHILE;

WOKE_UP_AT := V_TIME_TO_WAKE ;

END

Regards,

Amit