Link to home
Start Free TrialLog in
Avatar of brgdotnet
brgdotnetFlag for United States of America

asked on

Best code for creating a new column

I have two different blocks of sql code which basically perform the same functionality. One queries SYS.COLUMNS and the other queries
INFORMATION_SCHEMA.COLUMNS.
Which query is better to use, #1 or #2 ? Also ignore the fact that the second query uses parameters for the table name and column name instead of the hard coding which occurs in #1.


(1)
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'ROTOCOST' AND Object_ID = Object_ID())
                  BEGIN
                        EXEC('ALTER TABLE CUSTOMERS ADD ROTOCOST CHAR(9)')
                  END




(2)
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = @NewColName AND TABLE_NAME =  @TableName)
                        BEGIN
                              SET @sql = 'ALTER TABLE ' + @TableName + ' ADD ' +  @NewColName + ' ' + ' CHAR(9) '
                              EXEC(@sql)
                        END
ASKER CERTIFIED SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of brgdotnet

ASKER

Thank you.