Stored Procedure to Pause Execution

,

Ever needed to pause in a SQL script? Or wanted to simulate a long running process for testing? This stored procedure accepts an integer and pauses for that many seconds.

/*************************
EXAMPLE:
This will pause for 1 minute.
spPause 60
*************************/

create procedure spPause
(
@iNoSec int = 0
)
as

declare 
	@dEndTime datetime,
	@x int

set nocount on
select 
	@dEndTime = dateadd(s, @iNoSec, getdate()),
	@x = 0

print convert(varchar, getdate(), 108)
while getdate() < @dEndTime
	set @x = @x + 1	

print 'Cycles: ' + convert(varchar, @x)
print convert(varchar, getdate(), 108)
set nocount off

Rate

Share

Share

Rate