eugene.pipko (10/3/2012)
Thanks for replies,I don't use SSIS.
Not sure if it makes sense, but I was hoping to place the check for number of rows in table A (function call below) at the beginning of the second step, but it fails:
CREATE FUNCTION [dbo].[num_rows_in_A_fn] ()RETURNS int
BEGIN
DECLARE @num_rows int
SET @num_rows = (select COUNT(*) from A)
if @num_rows = 0
begin
WAITFOR DELAY '00:00:10';
-- call itself
exec [dbo].[num_rows_in_A_fn]
end
RETURN @num_rows
END
------
Msg 443, Level 16, State 14, Procedure num_rows_in_queue_fn, Line 11
Invalid use of a side-effecting operator 'WAITFOR' within a function.
------
Thanks,
Eugene
If you want a wait loop, just do the check in the loop & exit when it passes. Do it all within the same sp - something like this:
CREATE PROCEDURE [dbo].[RunJob] AS
BEGIN
SET NOCOUNT ON;
DECLARE @num_rows int;
SET @num_Rows = 999;
WHILE @num_rows > 0
BEGIN
SET @num_rows = (select COUNT(*) from A);
if @num_rows > 0
WAITFOR DELAY '00:00:10';
-- Possibly add some code to prevent an infinite loop?
END
-- Call job:
exec [dbo].[TheJobYouWantToRun]
END