SQL SERVER – Database Testing and Waitfor Delay – Notes from the Field #099

Database testing has become something of an obsession for us at Linchpin People. To test ETL processes, I often write mocking procedures that can simulate data flowing in from clients. When I first began doing this years ago, I’d set up SQL Agent jobs to invoke the mocking procedures on a regular frequency. However, that’s fairly cumbersome to set up so I started writing scripts like this instead:

WAITFOR DELAY '00:00:03';
EXEC sp_InsertMockTransaction;
GO 100


This code uses the WAITFOR command which many developers already understand. If you’ve never seen or heard of WAITFOR before, it’s fairly straightforward. In the code shown above, the DELAY option is used to induce a delay of three seconds. Afterwards, the mocking procedure runs.

The last line of the script is not as familiar to developers, I’ve found. We’ve all seen and used the GO phrase but as it turns out, it’s not part of the T-SQL language. GO is a batch separator that tells SSMS or the SQLCMD processor to forward the current batch of commands to an instance of SQL Server. What many developers don’t realize is that the GO batch separator can accept a positive integer parameter that will loop over the batch that many times.

In SSMS, the code shown above will invoke the mocking procedure 100 times, pausing for three seconds in between. This is a simple but effective way to simulate the input I’ll need for my tests. Of course, this pattern comes in handy for all sorts of uses, not just testing. Hopefully you’ll find simple trick as useful as I have.