Help with IF EXISTS and non existent column.

Question

Help with IF EXISTS and non existent column.

Rin Sitah

SSCrazy

Points: 2180
More actions
September 5, 2012 at 10:46 am

#263816

Hi there,
Im trying to do an update on a table but make sure the column exists before we do the update.
We do the check to see if the column exists: It does not exist, so the code should just end.
But we are getting an error "Invalid column name 'COLUMN'."
(Im just using TABLE and COLUMN as place holders for the real column and table names- they are not SQL reserved words etc..)
IF (EXISTS (SELECT * FROM sys.columns WHERE Name = N'COLUMN' AND Object_ID = Object_ID(N'[dbo].
') ))
BEGIN
PRINT 'Inside IF Statement because Column exists...'
Update dbo.
Set [COLUMN] = 'NewValue' Where [COLUMN]=1
END
If we comment out the Update, the query compiles fine.
Is there a way to get the query to run? or should we look at another way of ensuring that the column exists before doing an update?
Can anyone explain to me why its behaving like this? It looks like SQL is pre compiling everything and sees that the update statement will fail because the column its acting on dosnt exist, but we are handling that with the "if exists" part...

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

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

Luis Cazares SSC Guru Points: 183686 More actions · Answer 1

Are you doing that in a Stored Procedure? It might not work since it will verify that all the code is correct to compile it. I might be wrong, but that's what I remember.

If you need to do that, could you use dynamic SQL? I won't suggest any code, since it seems obvious you're not posting real code in here.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Rin Sitah SSCrazy Points: 2180 More actions · Answer 2

Thanks for the reply.

No- its just being run as a SQL query from Enterprise Manager.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

I think this is what you are looking for (if not exactly, should be close enough):

-- test table and test data

--CREATE TABLE ssc_test

--(

--x int,

--y int,

--z varchar(20)

--)

--INSERT INTO ssc_test

--SELECT 1,1,'old value'

--UNION

--SELECT 5,5,'blah'

DECLARE @colnamevarchar(20)='zzz',

@tblnamevarchar(20)='ssc_test',

@oldvalvarchar(20)='old value',

@newvalvarchar(20)='new value',

@sqlcmdvarchar(100);

IF EXISTS

(

SELECT * FROM sys.columns

WHERE Name=@colname -- column name exists

AND Object_ID(@tblname) IS NOT NULL

)

BEGIN

PRINT 'Column and table exist. Yay.'

SET @sqlcmd='UPDATE '+@tblname+' SET '+@colname+'='''+@newval+''' WHERE '+@colname+'='''+@oldval+''''

--PRINT @sqlcmd

EXEC(@sqlcmd)

RETURN;

END

RETURN;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Luis Cazares (9/5/2012)
Are you doing that in a Stored Procedure? It might not work since it will verify that all the code is correct to compile it. I might be wrong, but that's what I remember.
If you need to do that, could you use dynamic SQL? I won't suggest any code, since it seems obvious you're not posting real code in here.

Actually, SQL Server has to complie all of the SQL commands for any[/b] SQL batch, not just stored procedures. And yes, that is exactly why this error is occurring. And yes, the solution is to "hide" the optional SQL as a string inside of Dynamic SQL.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]