After creating a stored procedure and querying the table created,returns 'Invalid object name&#-CodePudding

I had written and successfully created a stored procedure by the query

CREATE PROCEDURE [dbo].[Table_Load] 
AS
BEGIN
    SET NOCOUNT ON 

    DECLARE @Row_Count_Inserted BIGINT 

    DROP TABLE IF EXISTS DBB.dbo.Table;

    SELECT * 
    INTO DBB.dbo.Table
    FROM
        (SELECT * 
         FROM DBB.dbo.customer_table) y

    SET @Row_Count_Inserted = @@RowCount

    SELECT @Row_Count_Inserted Row_Count_Inserted
END

This shows that the stored procedure is created and is present in the database. But when I query the table 'Table' using

  SELECT * FROM DBB.dbo.Table

I get an error

Invalid object name

How can I solve this issue? I have refreshed the database as well but it does not work.

CodePudding user response：

Here is your procedure greatly simplified to remove a lot of extra code. Assuming you have the table customer_table this will work just fine.

CREATE or alter PROCEDURE [dbo].[Table_Load] As
Begin
    SET NOCOUNT ON 

    drop table If Exists dbo.MyTable;

    Select * 
    into MyTable
    from customer_table

    select Row_Count_Inserted = @@RowCount
End

GO

exec Table_Load

GO

select * from MyTable