SQL Error 101 - Query not allowed in Waitfor

If you’re using the WAITFOR statement in SQL Server, and you get the following error, it’s probably because you’re providing a query as its argument.

Msg 101, Level 15, State 1, Line 2

Query not allowed in Waitfor.

The WAITFOR statement doesn’t accept queries for its "wait for" period. It only accepts a specific time or an interval.

Actually, it does accept RECEIVE statements, but this is only applicable to Service Broker messages, so if you’re not using Service Broker messages, the above error is quite self-explanatory.

Example:

When you incorrectly trying to use a query in WAITFOR statement

waitfor(SELECT firstname from [dbo].[Employee])

Result:

Msg 101, Level 15, State 1, Line 2

Query not allowed in Waitfor.

To overcome this issue, you’ll need to provide an actual time or a time delay.

For example, this will work.

WAITFOR DELAY '00:00:10';SELECT firstname from [dbo].[Employee]

Re-throwing exception generated by RAISERROR

 You can re-throw error that you catch in CATCH block using TRHOW statement:

DECLARE @msg nvarchar(50) = 'Here is a problem! Area: ''%s'' Line:''%i'''
BEGIN TRY
	print 'First statement';
	RAISERROR(@msg, 11, 1, 'TRY BLOCK', 2);
	print 'Second statement';
END TRY
BEGIN CATCH
	print 'Error: ' + ERROR_MESSAGE();
	THROW;
END CATCH

Output:

First statement

Error: Here is a problem! Area: 'TRY BLOCK' Line:'2'

Msg 50000, Level 11, State 1, Line 4

Here is a problem! Area: 'TRY BLOCK' Line:'2'

THROW is similar to RAISERROR with following differences:

• Recommendation is that new applications should use THROW instead of RASIERROR.
• THROW can use any number as first argument (error number), RAISERROR can use only ids in sys.messages view
• THROW has severity 16 (cannot be changed)
• THROW cannot format arguments like RAISERROR. Use FORMATMESSAGE function as an argument of RAISERROR if you need this feature.