SQL Server – Update Field Added from ALTER TABLE Statement

sql serversql-server-2008-r2t-sql

I am attempting to update a field that was just added from an ALter statement. I am receiving an error of:

Msg 207, Level 16, State 1, Procedure TestProc, Line 436
Invalid column name 'discountamount'.

This is my syntax that I am attempting to use – what would be the proper way to update a field just added in a stored procedure?

ALTER TABLE [dbo].msnbc
ADD saleamount float,
discountamount float;

UPDATE ftri
SET ftri.discountamount = fji.Totaldiscountamount
FROM [dbo].msnbc ftri
INNER JOIN [dbo].halfbaked fji
ON ftri.saleid = fji.saleid

Best Answer

If you need to do this in a stored procedure, your update has to be dynamic.

Here's a demo in case you get lost on the way.

Create a simple table:

USE tempdb;

CREATE TABLE dbo.whatever
(
    Id INT
);

INSERT dbo.whatever ( Id )
VALUES ( 1 );

GO

This is about what your stored procedure will have to look like:

CREATE PROC dbo.dosomething
(
    @i INT
)

AS
    BEGIN

        DECLARE @sql NVARCHAR(MAX) = N'';

        IF NOT EXISTS (   SELECT *
                          FROM   sys.columns AS c
                          WHERE  OBJECT_NAME(c.object_id) = 'whatever'
                                 AND c.name = 'something' )
            BEGIN
                ALTER TABLE dbo.whatever
                ADD something FLOAT;
            END;

        IF NOT EXISTS (   SELECT *
                          FROM   sys.columns AS c
                          WHERE  OBJECT_NAME(c.object_id) = 'whatever'
                                 AND c.name = 'soforth' )
            BEGIN
                ALTER TABLE dbo.whatever
                ADD soforth FLOAT;
            END;

        SELECT @sql = N'
        UPDATE dbo.whatever
        SET something = @s_i,
            soforth = @s_i
        WHERE Id = @s_i
        ';

        EXEC sys.sp_executesql @sql, N'@s_i INT', @s_i = @i;

    END;

There won't be any errors because the dynamic SQL isn't compiled at this stage.

Then you can run it:

EXEC dbo.dosomething @i = 1;

And validate results:

SELECT *
FROM   dbo.whatever AS w;


+----+-----------+---------+
| Id | something | soforth |
+----+-----------+---------+
|  1 |         1 |       1 |
+----+-----------+---------+

Hope this helps!

Related Question