SQL - Add column and set value after IF NOT EXISTS

Question

I'm having trouble with an Alter Table if a column doesn't exist.

This is my code:

DECLARE @appId INT
DECLARE @cursor CURSOR

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'MinMktIdeaParticipants' AND TABLE_NAME = 'mkIdeaCategories')
BEGIN
    ALTER TABLE mkIdeaCategories
        ADD MinMktIdeaParticipants int NOT NULL DEFAULT 1
END

IF EXISTS(SELECT Value FROM appConfiguration WHERE CodeId = 16 AND AppConfigurationTypeId = 3 AND ModuleId = 10)
BEGIN
    SET @cursor = CURSOR FAST_FORWARD
    FOR
        SELECT ApplicationId FROM mkMarket
        OPEN @cursor
            FETCH NEXT FROM @cursor INTO @appId
                WHILE (@@Fetch_Status >= 0)
                BEGIN
                    UPDATE 
                        mkIdeaCategories
                    SET MinMktIdeaParticipants = (SELECT Value FROM appConfiguration 
                        WHERE ApplicationId = @appId AND CodeId = 16 AND AppConfigurationTypeId = 3 AND ModuleId = 10)
                    WHERE IdeaCatId IN 
                        (select distinct(theme.IdeaCatId) from dbo.mkIdeaCategories theme
                        inner join dbo.mkMarketIdeaCategories mic ON theme.IdeaCatId = mic.IdeaCatId
                        inner join mkMarket m on m.MarketId=mic.MarketId 
                        WHERE m.ApplicationId = @appId)
                    FETCH NEXT FROM @cursor INTO @appId
                END
        CLOSE @cursor
    DEALLOCATE @cursor
    DELETE * FROM appConfiguration WHERE CodeId = 16 AND AppConfigurationTypeId = 3 AND ModuleId = 10
END

I don't think there are any errors. For some reason, the output error is

Msg 207, Level 16, State 1, Line 53
Invalid column name 'MinMktIdeaParticipants'.

I've already searched for similar questions, but couldn't find an answer that solved the problem.

You might find this helpful http://stackoverflow.com/questions/15740997/alter-table-then-update-in-single-statement — Giorgos Betsos, Mar 04 '15 at 13:32

score 2 · Accepted Answer · answered Mar 04 '15 at 13:29

2

When SQL Server processes a script, there are two phases. The first phase is compilation. The second is execution.

The error that you are getting is a compilation error. All the code is compiled, regardless of the if conditions. So, you are getting an error because the column doesn't exist. The column wouldn't be created until the execution phase.

One solution is to change the second part of the code to dynamic SQL, using exec (or better yet exec sp_executesql) to execute the code.

answered Mar 04 '15 at 13:29

Gordon Linoff

1,242,037
58
646
786

Thanks, that did the trick! For those who possibly find a similar issue, the solution was to declare both appId and cursor inside an exec executesql N' ... ' after the alter table statement. – João Colucas Mar 04 '15 at 14:10

score 1 · Answer 2 · answered Mar 04 '15 at 13:41

Gordon Linoff's solution will work. Another way to achieve what you want is to wrap your whole statement in a stored procedure.

this will fail:

select * from nonexisting_table

this will create a stored proc:

create procedure nonsense
as
begin
select * from nonexisting_table
end

BUT there are errors in your query:

SET @cursor = CURSOR FAST_FORWARD

To create a fast forward cursor in sql server you have to declare it. Thats my favourite example from mssqltips.com:

DECLARE db_cursor CURSOR FAST_FORWARD FOR  
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Thanks for your reply! Finally solved the issue with a exec sp_executesql! — João Colucas, Mar 04 '15 at 14:10

SQL - Add column and set value after IF NOT EXISTS

2 Answers2