Avatar of mrbass78
Flag for United States of America

asked on 

Stored Proc failing due to Binding Errors

I have a stored procedure (see code below) that does a series of drops and insert into's.  Each consecutive view is built on the previously created table.  When I run the proc I get "(12679 row(s) affected)

Server: Msg 208, Level 16, State 1, Procedure rv_BOM_Cost_Blowout_03_NEW, Line 3
Invalid object name 'dbo.tbl_BOM_Cost_Blowout_02_Data'.
Server: Msg 4413, Level 16, State 1, Procedure spRefreshBOMCostBlowoutTables, Line 10
Could not use view or function 'rv_BOM_Cost_Blowout_03_New' because of binding errors."

If I execute the statements line by line it works just fine.  I know that's it's probably stepping on itself and trying to do the next drop before the first completes, but how do I avoid this in the proc?

Thanks in advance.
CREATE PROCEDURE spRefreshBOMCostBlowoutTables as
drop table tbl_BOM_Cost_Blowout_Data
select * into tbl_BOM_Cost_Blowout_Data from rv_BOM_Cost_Blowout
drop table tbl_BOM_Cost_Blowout_02_Data
select * into tbl_BOM_Cost_Blowout_02_Data from rv_BOM_Cost_Blowout_02_New
drop table tbl_BOM_Cost_Blowout_03_Data
select * into tbl_BOM_Cost_Blowout_03_Data from rv_BOM_Cost_Blowout_03_New
drop table tbl_BOM_Cost_Blowout_03b_Data
select * into tbl_BOM_Cost_Blowout_03b_Data from rv_BOM_Cost_Blowout_03b_New
drop table tbl_BOM_Cost_Blowout_04_Data
select * into tbl_BOM_Cost_Blowout_04_Data from rv_BOM_Cost_Blowout_04_New
drop table tbl_BOM_Cost_Blowout_05_Data
select * into tbl_BOM_Cost_Blowout_05_Data from rv_BOM_Cost_Blowout_05_New
drop table tbl_BOM_Cost_Blowout_05_Inventory_Data
select * into tbl_BOM_Cost_Blowout_05_Inventory_Data from rv_BOM_Cost_Blowout_05_Inventory_New
drop table tbl_BOM_Cost_Blowout_05_NoYield_Data
select * into tbl_BOM_Cost_Blowout_05_NoYield_Data from rv_BOM_Cost_Blowout_05_NoYield_New
drop table tbl_BOM_Cost_Blowout_06_Data
select * into tbl_BOM_Cost_Blowout_06_Data from rv_BOM_Cost_Blowout_06_NEW
drop table tbl_BOM_Cost_Blowout_06_v3_Data
select * into tbl_BOM_Cost_Blowout_06_v3_Data from rv_BOM_Cost_Blowout_06_v3_NEW
drop table tbl_BOM_Cost_Blowout_06_v3_NoYield_Data
select * into tbl_BOM_Cost_Blowout_06_v3_NoYield_Data from rv_BOM_Cost_Blowout_06_v3_NoYield_NEW
drop table tbl_BOM_Cost_RawPackaging_Data
select * into tbl_BOM_Cost_RawPackaging_Data from rv_BOM_Cost_RawPackaging_NEW

Open in new window

Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment

8/22/2022 - Mon