2

I have a code that looks something like this

declare @query = '
   Alter Table Temp
   Add NewColumn int

   update Temp
   set NewColumn = 100'

exec (@query)

It will give error NewColumn does not exist. But if I run this normally without using @query = '' it runs fine. Also, what is it called when you run the code like this with the @query = ' text ' then exec?

0

3 Answers 3

5

You are executing them as one batch in dynamic SQL. So the compiler will not allow it, because at compilation time the column does not exist yet.

It is not true that it would work when executed together. you are executing them line-by-line in separate batches.

   Alter Table Temp
   Add NewColumn int
   update Temp
   set NewColumn = 100
   Alter Table Temp
   Add NewColumn int

   update Temp
   set NewColumn = 100

So you need to do this

Alter Table Temp
Add NewColumn int;

exec sp_executesql N'
   update Temp
   set NewColumn = 100
';

db<>fiddle

1
  • Well, but add column does not have to called by exec at all......... And call sp_executesql does not have to Declare a sql string variable. Just write exec sp_executesql N'Update ....' Jun 2, 2023 at 2:57
3

For this scenario you can just do it the addition of the column and assigning the values in one statement

ALTER TABLE Temp
  ADD NewColumn INT NULL CONSTRAINT DF_Temp_NewColumn DEFAULT 100 WITH VALUES  /* or NOT NULL if desired*/

Then you don't need any separate update statement referencing this column so no compile issues.

And this can be more efficient in some editions of SQL Server where they implement this as just a metadata only change.

If you don't want 100 to be used as a column default ongoing then just include

ALTER TABLE Temp
DROP DF_Temp_NewColumn

in the script.

6
  • This is optional solution, but it will be filled by default value if not explicitly specified value of this field when insert. Based on the asker's needs, I don't think it need a default value for this added field. Jun 5, 2023 at 9:15
  • @Mr.Squirrel.Downy - they can drop the default afterwards if they don't want it around long term. Jun 5, 2023 at 9:22
  • Then it's not "one statement", LOL. (you can add the drop default value statement) Jun 5, 2023 at 9:24
  • 1
    The selling point of this is not that it is "one statement" - it is that can be done as a metadata only change without updating all rows and does not get any compile issues forcing hiding statements in dynamic SQL. The "one statement" is only relevant in that the second statement for the Update approach is the one that causes the compile issue Jun 5, 2023 at 9:25
  • 1
    It works for the question that was asked. This is the question that I am answering. It does require the value to be a "runtime constant" that is the same for all rows Jun 5, 2023 at 9:54
0

Looks like you want to add new column and fill it in one execution.
Based on the @Charlieface answer but lighter:

-- check the column if not exists
IF COL_LENGTH('Temp','NewColumn') IS NULL
BEGIN
    ALTER TABLE [Temp] ADD [NewColumn] int
    EXEC sp_executesql N'UPDATE [Temp] SET [NewColumn] = 100'
END
1
  • For someone down vote me: Charlieface was impoved his answer after I comment it. But I add this answer before his edit. I AM NOT COPIED HIS ANSWER! Jun 6, 2023 at 2:51

Your Answer

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

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