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

Question

1.00/5 (1 vote)

See more:

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

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

SQL

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

What I have tried:

when make drop from server direct by hand without procedure
then exec

[Parts].[sp_get_parts]

issue not display again

SQL

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

Posted 8-Nov-21 22:30pm

ahmed_sa

Updated 9-Nov-21 3:08am

Add a Solution

Comments

Richard Deeming 9-Nov-21 5:14am

Dropping non-temporary tables from a stored procedure is a sure sign you're doing something wrong.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Answer 1 · 2021-11-08T23:31:00

Solution 1

Dropping tables means deleting them from the database.

Maybe you are searching for TRUNCATE TABLE (Transact-SQL) - SQL Server | Microsoft Docs[^] instead? Truncating means deleting any line in the table, but leaving the table itself alone.

Posted 8-Nov-21 23:31pm

phil.o

Comments

ahmed_sa 9-Nov-21 6:22am

i mean drop issue when i make drop on first of procedure it must not give me
invalid column name

phil.o 9-Nov-21 6:50am

If you delete (drop) the table, there is no column left, since there is no table to contain it.
It remains the possibility of something wrong in your secret logic, maybe you are recreating the table there, maybe not... As we can't see, we can't guess. All we can see is that you are dropping the table, and then performing a query against the same table that you just dropped. Which cannot work.

phil.o 9-Nov-21 10:29am

OK after re-reading the whole thing I think I get it.
You need to terminate statements and tell the engine to specifically process before continuuing:

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;

GO
-- the rest of the SP here

ahmed_sa 9-Nov-21 13:12pm

i will use go inside procedure or outside

CHill60 · Answer 2 · 2021-11-09T03:08:00

The version of [Parts].[sp_get_parts] that you have shared does not contain any references to column onlineid so executing it cannot possibly produce an error "invalid column name onlineid". You must be getting that error in some code that you have not shared with us.

The only columns in the table Extractreports.dbo.PartGeneration are partid and companyname - it doesn't matter if you have added other columns before calling the SP because you literally destroy the table before doing anything else in that SP - so whatever you did before calling the SP is not available to you afterwards.

If you do something like ...
Alter Extractreports.dbo.PartGeneration to add column onlineid
execute [Parts].[sp_get_parts]
select * from [Parts].[sp_get_parts]

You will see there are only those two columns.

You have several options, for example:

1. Do not drop the table, just truncate it (as advised in Solution 1) and change the way you insert data e.g.

SQL

INSERT into Extractreports.dbo.PartGeneration (partid, companyname) select partid,companyname from dbo.parts

2. Or, Drop the table but include onlineid in the select that recreates it e.g.

SQL

select partid,companyname,0 as onlineid into Extractreports.dbo.PartGeneration from dbo.parts