CODEWITHSUNDEEP
sql-server
KnightsOfTheRoun
KnightsOfTheRoun

Reputation: 155

Inalid column name after creating

I am creating SQL calls (SQL Server 17) on the fly from a C# program to update columns if they have invalid settings (size, datatype, ect). I am creating a new column and transferring data to the new column.

I constantly get an error saying Invalid Column Name (Line 3 in example), even though it follows the create statement, and the entire thing is wrapped in an IF that isn't even called most of the time.

I realize the "reason" is the TEMP column doesn't exist at the time of the call, but if the line is actually processed it would. Any suggestions on how to resolve this would be great.

SQL example:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AD' and COLUMN_NAME = 'ADD1' AND (data_type <> 'varchar' OR character_maximum_length <> '100' OR is_nullable <> 'no' OR (column_default IS NOT NULL AND column_default <> '(null)') ))
BEGIN
    ALTER TABLE [dbo].[AD] ADD [tempADD1] [varchar]  (100) NOT NULL ;
    UPDATE [dbo].[AD] SET tempADD1 = ADD1;

IF EXISTS (SELECT * FROM sysobjects s JOIN syscolumns c ON s.parent_obj = c.id WHERE s.xtype = 'd' AND c.cdefault = s.id AND parent_obj= object_id('AD') AND c.name ='tempADD1')
BEGIN
    DECLARE @find varchar(150);
    SET @find = (SELECT s.name FROM sysobjects s JOIN syscolumns c ON s.parent_obj = c.id
    WHERE s.xtype = 'd' AND c.cdefault = s.id AND parent_obj= object_id('AD') AND c.name ='tempADD1');

    EXEC('ALTER TABLE [dbo].[AD] DROP ' + @find + ';');
END

ALTER TABLE [dbo].[AD] DROP COLUMN [tempADD1];

EXEC sp_rename 'AD.tempADD1', 'ADD1', 'COLUMN'
END

Upvotes: 0

Views: 48

Answers (2)

rschoenbach
rschoenbach

Reputation: 535

If the column doesn't exist at the time of the call and causing an error, wrap the call in dynamic TSQL. This will delay the compilation until execution.

SET @SQL = 'ALTER TABLE [dbo].[AD] DROP COLUMN [tempADD1];'
EXEC SP_EXECUTESQL @SQL;

Upvotes: 0

Dave C
Dave C

Reputation: 7392

Couple options.

1) Make separate calls to the database (less efficient).

First call, creates column. Second call, updates column (it exists now, so no error).

2) Make one call to the database, and "hide" the query so it can't be validated, in a nested dynamic SQL query.

ex:

DECLARE @SQL NVARCHAR(MAX)

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AD' and COLUMN_NAME = 'ADD1' AND (data_type <> 'varchar' OR character_maximum_length <> '100' OR is_nullable <> 'no' OR (column_default IS NOT NULL AND column_default <> '(null)') ))
BEGIN
    ALTER TABLE [dbo].[AD] ADD [tempADD1] [varchar]  (100) NOT NULL ;
    SET @SQL='UPDATE [dbo].[AD] SET tempADD1 = ADD1;'
    EXEC SP_EXECUTESQL @SQL

...
...

Lastly, I would avoid doing this:

EXEC('ALTER TABLE [dbo].[AD] DROP ' + @find + ';');

This should be executed in the same manner I showed above using SP_EXECUTESQL instead.

Upvotes: 2

Related Questions