when make drop to table already exist on begin of procedure it not working issue until i do by hand ?

Question

I work on SQL server 2012 i face issue
when make drop to table on begin of procedure it not working issue until I do by hand ?
so if i alter table Extractreports.dbo.PartGeneration by adding new column as onlineid on other place
then execute [Parts].[sp_get_parts] it give me error invalid column name onlineid
ok but i make drop on start of procedure why error display
so when i go on server then execute following statment as below
and run

IF OBJECT_ID('Extractreports.dbo.PartGeneration') IS NOT NULL
    DROP TABLE Extractreports.dbo.PartGeneration

    IF OBJECT_ID('Extractreports.dbo.getInsertedRows') IS NOT NULL
    drop table Extractreports.dbo.getInsertedRows

when execute again error not display
it working only when go every time and execute from server for drops tables

create Proc [Parts].[sp_get_parts]
    AS

        BEGIN

    IF OBJECT_ID('Extractreports.dbo.PartGeneration') IS NOT NULL
    DROP TABLE Extractreports.dbo.PartGeneration

    IF OBJECT_ID('Extractreports.dbo.getInsertedRows') IS NOT NULL
    drop table Extractreports.dbo.getInsertedRows
    --SOME LOGIC
select partid,companyname into Extractreports.dbo.PartGeneration from dbo.parts
select family,plname into Extractreports.dbo.getInsertedRows from dbo.parts
    END

so how to solve this issue

Answer 1

When you create a stored procedure, SQL Server will ignore if the procedure refers to non-existing tables, optimistically thinking that the table will appear at run-time. Which, in the case of temp table created in the procedure, can be very true.

However, once that SQL Server finds a query where all tables exist, it will go on and check that all columns you refer to exist, so that you have not made any typos.

If you are creating your table in the stored procedure, you need to drop it before you recreate it with a different definition of the table, or else you will get an error.

Answer 2

You can first create a stored procedure to delete the table：

CREATE PROCEDURE sp_dropifexists (@tableName VARCHAR(255))
AS
BEGIN
    DECLARE @SQL VARCHAR(MAX);
    SET @SQL = 'IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N''' + @tableName + ''') AND type = (N''U'')) DROP TABLE [' + @tableName + ']'

    EXEC (@SQL);

END

EXEC sp_DropIfExists 'Extractreports.dbo.PartGeneration'
EXEC sp_DropIfExists 'Extractreports.dbo.getInsertedRows'

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".