SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Create Column if doesn't exist
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

beatkeeper25
Starting Member

19 Posts

Posted - 09/10/2013 :  22:01:15  Show Profile  Reply with Quote
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

USA
37316 Posts

Posted - 09/10/2013 :  22:53:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

beatkeeper25
Starting Member

19 Posts

Posted - 09/11/2013 :  00:14:09  Show Profile  Reply with Quote
Can TRY CATCH be used this way?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 09/11/2013 :  00:41:33  Show Profile  Reply with Quote
As per my point of view, 2nd approach is the best way

--
Chandu
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 09/11/2013 :  03:43:51  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000