Using Exists in IF condition

Question

Using Exists in IF condition

sbohland

SSC Enthusiast

Points: 105
More actions
July 3, 2008 at 9:26 am

#184286

I am having issues trying to get mutliple existence checks within a single if or nested ifs to work.
Shown below in the first block is my attempt at using existence checks in nested IF statements. When these existence checks are run alone they work fine, but when I nest them they are not. Currently The StartDate column does not exist on my Promotion table and therefore I do not want the INSERT command to run, but it does every time. The second block shows the existence check that I am using does work by itself.
Thanks in advance for any help!
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PromotionSchedule]') AND type in (N'U'))
BEGIN
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[Promotion]') AND name = N'StartDate')
BEGIN
INSERT INTO [dbo].[PromotionSchedule] (PRId, EId, StartDate, EndDate, CreatedOn, syncTs)
select PRId, EId, StartDate, EndDate, CreatedOn, syncTs from dbo.Promotion
END
END
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[Promotion]') AND name = N'StartDate')
print 'startdate still present'
ELSE
print 'startdate does not exist'

Viewing 10 posts - 1 through 9 (of 9 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

I assume then that you are getting an error referring to the StartDate column so you assume that the existance check is not working and the INSERT statement is running and failing? What is happening here is that the compiler is throwing the error when it attempts to compile the code and you are referencing a column name that does not exist in your schema. The nested checks work fine.

To get this past the compiler, you'll have to run it as dynamic SQL, but beware, this has its own downsides as well. Why do you need the conditional check?

EXEC('INSERT INTO [dbo].[PromotionSchedule] (PRId, EId, StartDate, EndDate, CreatedOn, syncTs) select PRId, EId, StartDate, EndDate, CreatedOn, syncTs from dbo.Promotion')

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

GSquared SSC Guru Points: 260824 More actions · Answer 2

I agree that you are probably running into a compile error on this one.

What I'm wondering, though, is why do you need this code in the first place? It seems very strange to me.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sbohland SSC Enthusiast Points: 105 More actions · Answer 3

I am using the conditional check because the startdate column is being dropped from the table after we migrate data to new table. We have a script we run in development and I do not want it to error when run multiple times, since the second time around the startdate column would no longer be in the table.

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 4

I executed the code on my SQL Server and found no error (SQL Server 2005 Ent). Also, the insert statement executes according to the conditions.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 5

You are checking for the existence of the column in the [Promotion] table, and then inserting into the [PromotionSchedule] table... is that correct?

sbohland SSC Enthusiast Points: 105 More actions · Answer 6

Hello Ian:

Yes, I am checking for the existance of a column in the promtion table prior to inserting into the promotionschedule table. Later in the database script some columns are removed from the promotion table after the data from those columns has successfully been migrated to the new promotionschedule table.

sbohland SSC Enthusiast Points: 105 More actions · Answer 7

Ian:

some more info.. the promotionschedule table is being created in the same script file, so I am checking to make sure that it was created before the insert as well. This is when I run into the problem, when I try to use two existance checks. Is there a different way to check that the promotionschedule table was created and that the columns still exist on the promotion table? I am doing this checking because I want the script to be able to be executed repeatedly without erroring out.

Thanks!

sbohland SSC Enthusiast Points: 105 More actions · Answer 8

Here is the error I am receiving when I run the script file after the first successful run, in which the columns were removed from the promotion table.

Msg 207, Level 16, State 1, Line 6

Invalid column name 'StartDate'.

Msg 207, Level 16, State 1, Line 6

Invalid column name 'EndDate'.

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 9

You could use a union and select a field

if exists ( select ID from sysobjects where ...

UNION

SELECt ID from sysobjects)

but I don't see an issue with your code.

You might need separate batches, depending on what you do inside.