SQL Server Stored Procedure - Wait for a Table to be Created
-
Hello all. I have the following Stored Procedure:
ALTER PROCEDURE [EventPortion].[24Start]
ASIF object_id('[IntegratedTest1].[EventPortion].[244Q1]') is not null
DROP TABLE [IntegratedTest1].[EventPortion].[244Q1]IF object_id('[IntegratedTest1].[EventPortion].[244Q2]') is not null
DROP TABLE [IntegratedTest1].[EventPortion].[244Q2]IF object_id('[IntegratedTest1].[EventPortion].[244Q3]') is not null
DROP TABLE [IntegratedTest1].[EventPortion].[244Q3]SELECT * Into [IntegratedTest1].[EventPortion].[244Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q1
While (object_id('[IntegratedTest1].[EventPortion].[244Q1]') is null)
Begin
WAITFOR DELAY '00:00:00.001';
EndBegin
WAITFOR DELAY '00:00:01.000';
EndSELECT * Into [IntegratedTest1].[EventPortion].[244Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q2
While (object_id('[IntegratedTest1].[EventPortion].[244Q2]') is null)
Begin
WAITFOR DELAY '00:00:00.001';
End
SELECT * Into [IntegratedTest1].[EventPortion].[244Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q3I want this code to drop the 3 tables if they exist. This works. Then run the first Select Into. IntegratedTest1.Event.MOE_2_4_DE_4_Q2 depends on (Selects from) 244Q1. The problem that I am having is that the second Select Into is being executed and I am getting a binding error that 244Q1 doesn't exist. So, the While Is Null Wait For loops are not working. If I just execute the Wait For 1 second delay, that does take 1 second to execute. If I just run the first select into and the 1 second delay, it takes about 1 second to execute. But if I run the first and second select into with the delays in between, there is no delay. I immediately get the binding error. Is there anything I can do about this seemingly odd behavior? Thanks, David Edit: I figured out a solution and wanted to post it. I modified the stored procedure to:
ALTER PROCEDURE [EventPortion].[24Start]
AS
DROP TABLE [IntegratedTest1].[EventPortion].[244Q1]
DROP TABLE [IntegratedTest1].[EventPortion].[244Q2]
DROP TABLE [IntegratedTest1].[EventPortion].[244Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[244Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q1
SELECT * Into [IntegratedTest1].[EventPortion].[244Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q2
SELECT * Into [IntegratedTest1].[EventPortion].[244Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q3
DROP TABLE [IntegratedTest1].[EventPortion].[245Q1]
DROP TABLE [IntegratedTest1].[EventPortio