wait for delay inside a fuction

Question

Post reply

wait for delay inside a fuction

wannabe1

SSCommitted

Points: 1960
More actions
January 14, 2015 at 7:44 am

#293791

hello guys,
As WAITFOR DELAY '00:00:05' is not allowed inside a function, can you please let me know if there is any other simple way to accomplish the wait. - Thanks
this is the code
BEGIN
DECLARE @tempTable TABLE (spid int, ecid int, cpu int,physical_io int,memusage int)
INSERT INTO @tempTable
select spid, ecid, cpu, physical_io, memusage from master.dbo.sysprocesses
-- WAITFOR DELAY '00:00:03'
INSERT INTO @ReturnTable
select top 40 s.spid,Dcpu=s.cpu - t.cpu,Dio=s.physical_io - t.physical_io,Dmem=s.memusage - t.memusage,
login_time,last_batch,status,hostname,program_name,cmd,nt_domain,nt_username,loginame
from master.dbo.sysprocesses s,@tempTable t where s.spid=t.spid and s.ecid=t.ecid order by Dcpu desc, Dio desc, s.spid
return
END

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Why would you want it in function? Whatever reason, it would be highly unrecommened.

However you can use dummy loop:

declare @dtStart datetime=getutcdate()

while datediff(second,@dtStart,getutcdate()) < 3

begin

set @dtStart = @dtStart

end

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

wannabe1 SSCommitted Points: 1960 More actions · Answer 2

i wanna wait for some time(seconds) to get a delta on resources and i want it as a function to sort based on columns

thanks for the reply.

Jeff Moden SSC Guru Points: 1004601 More actions · Answer 3

wannabe1 (1/14/2015)
i wanna wait for some time(seconds) to get a delta on resources and i want it as a function to sort based on columns
thanks for the reply.

You should do this in a stored procedure rather than a function. Doing a WHILE loop to hum around and do nothing for 3 seconds is pretty expensive CPU wise.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 4

wannabe1 (1/14/2015)
i wanna wait for some time(seconds) to get a delta on resources and i want it as a function to sort based on columns
thanks for the reply.

What does the code being in a function have to do with sorting the output?

Don Simpson

I'm not sure about Heisenberg.

wait for delay inside a fuction

Cookies on SQLServerCentral