Sql-server – Why will the ALTER and UPDATE not work in the same query in this specific instance

ddlsql servert-sqlupdate

I have a problem with a query where i am trying to ALTER a table by adding 3 columns to it, then updating each column with 1 specific piece of data.

I am aware there are several posts like this but every answer for them has not helped my specific situation e.g. "IF NOT EXISTS" or "COL_LENGTH" or adding "GO" in between the ALTER and UPDATE etc.

I still keep getting "Invalid Column Name" errors. Please see my code below:

CREATE PROCEDURE [dbo].[p_app_2_24_01]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
UPDATE [dbo].[app_Config] SET CFG_Value = '2.24.01'  WHERE CFG_KEY = 'VER'


ALTER TABLE [dbo].[app_table1] ADD [Bonus] int
ALTER TABLE [dbo].[app_table1] ADD [Allow] bit
ALTER TABLE [dbo].[app_table1] ADD [Discount] int

ALTER TABLE [dbo].[app_table2] ADD [Bonus] int
ALTER TABLE [dbo].[app_table2] ADD [Allow] bit
ALTER TABLE [dbo].[app_table2] ADD [Discount] int

Update [dbo].[app_table1] 
                set [Bonus]  = 999,
                Allow = 1,
                Discount = 100

Update [dbo].[app_table2] 
                set [Bonus]  = 999,
                Allow = 1,
                Discount = 100

Would anyone happen to have a great revelation to my increasingly frustrating problem? 🙂

Many thanks in advance,

Best Answer

Do you have the option to create the new columns outside of this stored procedure via change control?

Alternatively, you could use default constraints if your business requirements allow the columns to be not null:

CREATE PROCEDURE [dbo].[p_app_2_24_01]
AS
    -- SET NOCOUNT ON added to prevent extra result sets from
    UPDATE [dbo].[app_Config] SET CFG_Value = '2.24.01'  WHERE CFG_KEY = 'VER'


    ALTER TABLE [dbo].[app_table1] ADD [Bonus] int NOT NULL CONSTRAINT df_app_table1_Bonus DEFAULT (999);
    ALTER TABLE [dbo].[app_table1] ADD [Allow] bit NOT NULL CONSTRAINT df_app_table1_Allow DEFAULT (1);
    ALTER TABLE [dbo].[app_table1] ADD [Discount] int NOT NULL CONSTRAINT df_app_table1_Discount DEFAULT (100);

    ALTER TABLE [dbo].[app_table2] ADD [Bonus] int NOT NULL CONSTRAINT df_app_table2_Bonus DEFAULT (999);
    ALTER TABLE [dbo].[app_table2] ADD [Allow] bit NOT NULL CONSTRAINT df_app_table2_Allow DEFAULT (1);
    ALTER TABLE [dbo].[app_table2] ADD [Discount] int NOT NULL CONSTRAINT df_app_table2_Discount DEFAULT (100);
GO

If your columns must be nullable, then use the WITH VALUES clause:

CREATE PROCEDURE [dbo].[p_app_2_24_01]
AS
    -- SET NOCOUNT ON added to prevent extra result sets from
    UPDATE [dbo].[app_Config] SET CFG_Value = '2.24.01'  WHERE CFG_KEY = 'VER'


    ALTER TABLE [dbo].[app_table1] ADD [Bonus] int NULL CONSTRAINT df_app_table1_Bonus DEFAULT (999) WITH VALUES;
    ALTER TABLE [dbo].[app_table1] ADD [Allow] bit NULL CONSTRAINT df_app_table1_Allow DEFAULT (1) WITH VALUES;
    ALTER TABLE [dbo].[app_table1] ADD [Discount] int NULL CONSTRAINT df_app_table1_Discount DEFAULT (100) WITH VALUES;

    ALTER TABLE [dbo].[app_table2] ADD [Bonus] int NULL CONSTRAINT df_app_table2_Bonus DEFAULT (999) WITH VALUES;
    ALTER TABLE [dbo].[app_table2] ADD [Allow] bit NULL CONSTRAINT df_app_table2_Allow DEFAULT (1) WITH VALUES;
    ALTER TABLE [dbo].[app_table2] ADD [Discount] int NULL CONSTRAINT df_app_table2_Discount DEFAULT (100) WITH VALUES;
GO

Lastly, you could use sp_executesql to perform the updates. You are not introducing a SQL injection risk as the update values are not parameterised.

Related Question