Create Column if doesn't exist

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Create Column if doesn't exist

Author

Topic

beatkeeper25
Starting Member

27 Posts

Posted - 2013-09-10 : 22:01:15

I've come across a sproc that is supposed to create columns if they don't exist. Author used Try Catch, but its not working right.

BEGIN TRY
SELECT TOP 1 Column1 FROM TableA
END TRY
BEGIN CATCH
ALTER TABLE TableA ADD Column1
END CATCH

I'm trying this approach, but would like it not to throw any errors if the column exists.

IF NOT EXISTS (
SELECT *
FROM sys.columns
WHERE object_id = OBJECT_ID(N'TableA')
AND name = 'Column1')
BEGIN
ALTER TABLE TableA ADD Column1 INT NOT NULL
END

What's the best way to accomplish this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-10 : 22:53:02

You could put the ALTER TABLE inside dynamic SQL to hide it from the compiler.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

beatkeeper25
Starting Member

27 Posts

Posted - 2013-09-11 : 00:14:09

Can TRY CATCH be used this way?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-11 : 00:41:33

As per my point of view, 2nd approach is the best way

--
Chandu

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-09-11 : 03:43:51

This would be the best:


IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableA' AND COLUMN_NAME = 'Column1')
	ALTER TABLE TableA ADD Column1 INT NOT NULL

- Lumbago
My blog-> http://thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources