question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked Viorel-1 commented

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

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







sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Which line shows "invalid column name onlineid"?


0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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".


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.