CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Nile
Nile

Reputation: 1

IF EXISTS.... DROP not working in a stored procedure

I am stuck with the following stored procedure where I can't seem to get the IF EXISTS and DROP parts to work, leading to a failure in the SELECT INTO part.

Both database A and database B are on the same server, I have full permissions in both databases. The stored procedure is in database A.

I have copied the IF EXISTS syntax from somewhere (can't remember where) so I don't really understand the structure of it. I gathered the problem lies in the IF EXISTS statement because when I try and execute IF EXISTS component of the stored procedure, I get something if I have selected DatabaseB in the top left-hand corner drop-down box in Management Studio but if I have DatabaseA selected in there, I get nothing.

I have also tried to run similarly structured stored procedures in DatabaseA (where there is an IF EXISTS and DROP statements pointing to DatabaseB followed by a SELECT INTO from DatabaseA into DatabaseB) and I have got some to work before, while some others failed. I cant seem to pinpoint what is causing it to work sometimes and sometimes not.

USE [DatabaseA]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--DROP TABLE A if exists--
IF EXISTS (SELECT * FROM sys.objects  
           WHERE object_id = OBJECT_ID(N'DatabaseB.dbo.TableA') AND type IN (N'U'))
    DROP TABLE DatabaseB.dbo.TableA

--Select INTO TableA on DatabaseB--
SELECT *
INTO DatabaseB.dbo.TableA
FROM DatabaseA.dbo.TableA

Upvotes: 0

Views: 1180

Answers (1)

Ben Thul
Ben Thul

Reputation: 32707

I usually use if object_id('databaseB.dbo.TableA') is not null instead of the exists check to avoid having to fully qualify sys.objrcts.

Upvotes: 1

Related Questions