How do I put a pause in the middle of a query?

[JasonParr]

I need to have a query pause in the middle of executing. How do I make it do that? I am working on a server running MSSQL2000. Thanks

 

[mimi2]

I found this in books on line:
WAITFOR
Specifies a time, time interval, or event that triggers the execution of a statement block, stored procedure, or transaction.


Examples
A. Use WAITFOR TIME
This example executes the stored procedure update_all_stats at 10:20 P.M.

BEGIN
WAITFOR TIME '22:20'
EXECUTE update_all_stats
END

For more information about using this procedure to update all statistics for a database, see the examples in UPDATE STATISTICS.

B. Use WAITFOR DELAY
This example shows how a local variable can be used with the WAITFOR DELAY option. A stored procedure is created to wait for a variable amount of time and then returns information to the user as to the number of hours, minutes, and seconds that have elapsed.

CREATE PROCEDURE time_delay @@DELAYLENGTH char(9)
AS
DECLARE @@RETURNINFO varchar(255)
BEGIN
WAITFOR DELAY @@DELAYLENGTH
SELECT @@RETURNINFO = 'A total time of ' +
SUBSTRING(@@DELAYLENGTH, 1, 3) +
' hours, ' +
SUBSTRING(@@DELAYLENGTH, 5, 2) +
' minutes, and ' +
SUBSTRING(@@DELAYLENGTH, 8, 2) +
' seconds, ' +
'has elapsed! Your time is up.'
PRINT @@RETURNINFO
END
GO
-- This next statement executes the time_delay procedure.
EXEC time_delay '000:00:10'
GO

Here is the result set:

A total time of 000 hours, 00 minutes, and 10 seconds, has elapsed! Your time is up.


See Also

Control-of-Flow Language

datetime and smalldatetime

sp_who