[sql-server] Sleep Command in T-SQL?

Is there to way write a T-SQL command to just make it sleep for a period of time? I am writing a web service asynchronously and I want to be able to run some tests to see if the asynchronous pattern is really going to make it more scalable. In order to "mock" an external service that is slow, I want to be able to call a SQL server with a script that runs slowly, but isn't actually processing a ton of stuff.

Here is a very simple piece of C# code to test the CommandTimeout with. It creates a new command which will wait for 2 seconds. Set the CommandTimeout to 1 second and you will see an exception when running it. Setting the CommandTimeout to either 0 or something higher than 2 will run fine. By the way, the default CommandTimeout is 30 seconds.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;

namespace ConsoleApplication1
{
  class Program
  {
    static void Main(string[] args)
    {
      var builder = new SqlConnectionStringBuilder();
      builder.DataSource = "localhost";
      builder.IntegratedSecurity = true;
      builder.InitialCatalog = "master";

      var connectionString = builder.ConnectionString;

      using (var connection = new SqlConnection(connectionString))
      {
        connection.Open();

        using (var command = connection.CreateCommand())
        {
          command.CommandText = "WAITFOR DELAY '00:00:02'";
          command.CommandTimeout = 1;

          command.ExecuteNonQuery();
        }
      }
    }
  }
}

WAITFOR DELAY 'HH:MM:SS'

I believe the maximum time this can wait for is 23 hours, 59 minutes and 59 seconds.

Here's a Scalar-valued function to show it's use; the below function will take an integer parameter of seconds, which it then translates into HH:MM:SS and executes it using the EXEC sp_executesql @sqlcode command to query. Below function is for demonstration only, i know it's not fit for purpose really as a scalar-valued function! :-)

    CREATE FUNCTION [dbo].[ufn_DelayFor_MaxTimeIs24Hours]
    (
    @sec int
    )
    RETURNS
    nvarchar(4)
    AS
    BEGIN


    declare @hours int = @sec / 60 / 60
    declare @mins int = (@sec / 60) - (@hours * 60)
    declare @secs int = (@sec - ((@hours * 60) * 60)) - (@mins * 60)


    IF @hours > 23 
    BEGIN
    select @hours = 23
    select @mins = 59
    select @secs = 59
    -- 'maximum wait time is 23 hours, 59 minutes and 59 seconds.'
    END


    declare @sql nvarchar(24) = 'WAITFOR DELAY '+char(39)+cast(@hours as nvarchar(2))+':'+CAST(@mins as nvarchar(2))+':'+CAST(@secs as nvarchar(2))+char(39)


    exec sp_executesql @sql

    return ''
    END

IF you wish to delay longer than 24 hours, I suggest you use a @Days parameter to go for a number of days and wrap the function executable inside a loop... e.g..

    Declare @Days int = 5
    Declare @CurrentDay int = 1

    WHILE @CurrentDay <= @Days
    BEGIN

    --24 hours, function will run for 23 hours, 59 minutes, 59 seconds per run.
    [ufn_DelayFor_MaxTimeIs24Hours] 86400

    SELECT @CurrentDay = @CurrentDay + 1
    END

You can also "WAITFOR" a "TIME":

    RAISERROR('Im about to wait for a certain time...', 0, 1) WITH NOWAIT
    WAITFOR TIME '16:43:30.000'
    RAISERROR('I waited!', 0, 1) WITH NOWAIT