2

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
0

1 Answer 1

5

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.