when make drop to table already exist on begin of procedure it not working issue

Question

Post reply

when make drop to table already exist on begin of procedure it not working issue

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

November 9, 2021 at 11:25 am

#3949409

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

kaj SSCommitted Points: 1580 More actions · Answer 1

You probably have to insert a batch separator (the standard batch separator is GO) before your -- SOME LOGIC section.

tsql - SQL Server: What are batching statements (i.e. using "GO") good for? - Stack Overflow

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 2

ok but i think if i use go on stored procedure it will end execution

so what i do

kaj SSCommitted Points: 1580 More actions · Answer 3

Okay, yes, you are of course correct. GO is not a TSQL instruction, so it cannot be used in a stored procedure.

I would try to split the code into two procedures and call the first one from the second, i.e. put the DROP TABLE section into a separate procedure and call that one from your current procedure with an EXEC.

Mr. Brian Gale SSC-Insane Points: 24689 More actions · Answer 4

Alternately, rather than dropping the tables and recreating them (not a process I generally do in a stored procedure), how about doing a TRUNCATE/DELETE at the start and an INSERT INTO rather than a SELECT INTO?

Something along the lines of:

CREATE PROCEDURE [Parts].[sp_get_parts]
AS
        
BEGIN
        
     TRUNCATE TABLE [Extractreports].[dbo].[PartGeneration]
     TRUNCATE TABLE [Extractreports].[dbo].[getInsertedRows]
     --SOME LOGIC
INSERT INTO [Extractreports].[dbo].[PartGeneration]
 SELECT [partid],[companyname]
 FROM [dbo].[parts]

INSERT INTO [Extractreports].[dbo].[getInsertedRows] 
 SELECT [family],[plname] 
 FROM [dbo].[parts]
END

My logic here is that then when you initially create the tables, you can build up indexes on them to make the reporting go faster. I am assuming that "Extractreports" database is for reporting. Using the DROP TABLE statement would result in all indexes being removed. Using a TRUNCATE would keep all indexes and permissions and foreign keys (if any).

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.