How to Wait, Pause or Sleep

[ysrinu]

Hi,
in my stored procedure i want to wait/pause/sleep for few milliseconds.
I am not waiting for any action to complete, i just have to pause for a while.
How can i do this?
Thanks,
-srinivas yelamanchili

[joedsilva]

There's a tricky way I can think of ( or probably a bunch of tricky ways to explore)...

But why are trying to do it ? is it for debugging purposes ?

There could be a better way to do it that we might be able to suggest.

[Adeel_Chaudhry]

Hello,

You can also try UDF with a sleep function in it.

But again, whats the purpose for such activity?

Regards,

Adeel

[ysrinu]

My stored procedure is a recursive one, and it inserts the current timestamp for every call.
The procedure runs so fast that the timestamp is the same for two consecutive calls upto precision of 6seconds.
I therefore, want to wait for at least 0.000001 sec before the next call.
I know, i can use a new column and store counter value that is incremented for each call, but would prefer to use the timestamp instead.
UDFs are not supported and not recommended by our DBAs in our project.
Thanks,
-srinivas

[Adeel_Chaudhry]

couple of questions...one from where SPs are being called, some sort of front-end application or bteq etc? second why UDFs are not supported/recommended....this one is for my knowledge.

Regards,

Adeel

[Adeel_Chaudhry]

Can you also provide the part of SP code in which you are inserting a row?

[ysrinu]

Hi,
i am calling the SP from bteq. This shouldn't matter much, as i am only interested in keeping different timestamps in the debug table for
sp calls that run linearly.

No UDFs have ever been written yet, and the DBAs are not really in favor of this, i can't explain why.

When the SP calls itself recursively based on if condition, the rows inserted in the debug table have same timestamp for two consecutive calls,
and therefore want to sleep for a fraction of second before the sp call.

Here's the pseudo-code:

sp_sample(INOUT io_retcd)
{
if (...) then
...
insert into debug table (current_timestamp,...);

else

call sp_sample(...)
insert into debug table (current_timestamp,...);
end if

}

Thanks,
-srinivas

[Adeel_Chaudhry]

For such case you dont need to have sleep functionality, i'll suggest you to have a timestamp parameter for the SP and in the recursive call simply add 1 second to it or add 0.000001 second to it (as per your requirement).

At the top you will also have to check for the first time input, i.e. if the timestamp parameter is null, then just set the variable equal to current_timestamp.

HTH!

Regards,

Adeel

[joedsilva]

Few ways to approach.

Pass the timestamp as argument to the initial proc call, and then it can increment it and pass it on to the next call etc...

mySP(ts timestamp)
{
do stuff...
(if call again) call mySP(ts+1);
}

call mySP (current_timestamp(0));

Use a counter variable in the same way, pass 0 to the first call, and any next calls can increment and pass it on ...

mySP(cnter INTEGER)
{
do stuff...
(if call again) call mySP(cnter+1);
}

I would prefer the second approach.

[huwlewis]

Don't forget that the precision of current_timestamp is not 6 digits, even if you specify current_timestamp(6). Check out the precision (I think it is only 2 decimal digits normally) as adding 0.000001 seconds may make no difference, unless you sleep for at least 0.01 seconds.