1

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,

  • 6
    When the procedure is compiled, the columns do not exist, so you can't create the procedure. The database doesn't know that the procedure is creating them. You need to use dynamic SQL for that or split it in two procedures or maybe use default values for the new columns, then you don't need the updates at all – a_horse_with_no_name Oct 19 '16 at 6:08
  • 1
    Why would you want to create a Stored Procedure that you can only run once? – Mark Sinkinson Oct 19 '16 at 10:41
  • @a_horse_with_no_name - thank you for your suggestions, i'm going to attempt splitting the procedures (there are a lot of them, this is just an excerpt). The ultimate goal is to get these to run as part of an installer. – L.Moy Oct 21 '16 at 1:26
  • @Mark Sinkinson - that is a good question, its a part of an old process that im looking into improving :) – L.Moy Oct 21 '16 at 1:27
4

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.

  • @Andy Jones - thanks a lot for your suggestion there! this is also something i'm going to try along with splitting up the procedures – L.Moy Oct 21 '16 at 1:28
  • 2
    Many thanks to @Martin Smith. I have updated my answer to reflect your correct information. – Andy Jones Oct 21 '16 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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