brgdotnet
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
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
BEGIN
SET @sql = 'ALTER TABLE ' + @TableName + ' ADD ' + @NewColName + ' ' + ' CHAR(9) '
EXEC(@sql)
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER