sp_executesql: Add column with case statement

Question

This is an example code of what I would like to achieve, I want to add a column to a table with some specific code variables. However, I can't really grasp how the sp_executesql passes it through. Any help appreciated

DECLARE @SQLQUERY NVARCHAR (max)
DECLARE @Table VARCHAR (200) = 'Table_Name'
DECLARE @Column VARCHAR (200) = 'Column_Name'

DECLARE @Code1 int = 1
DECLARE @Code2 int = 2
DECLARE @Code3 int = 3

SET @SQLQUERY = 'ALTER TABLE' + @Table + 'ADD' @Column 
AS CASE WHEN FirstColumn IS NULL THEN @Code1
        WHEN SecondColumn IS NULL THEN @Code2
    ELSE @Code3

Exec sp_executesql @SQLQUERY

What exactly are you trying to achieve? Adding a computed column to your table? — ZLK, Sep 15 '16 at 08:54
Do you expect `ALTER TABLETable_NameADDColumn_Name` to work without spaces? Tip: The best practice when assembling object names into dynamic SQL statements is to use [`QuoteName()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. — HABO, Sep 21 '20 at 18:28

Monah · Answer 1 · 2016-09-15T09:10:36.193

0

you need to build the alter statement all as string and then execute it, for example your query should be like this:

SET @SQLQUERY = 'ALTER TABLE ' + @Table + ' ADD ' + @Column  + 
' AS CASE WHEN FirstColumn IS NULL THEN ' + str(@Code1) + 
       ' WHEN SecondColumn IS NULL THEN ' + str(@Code2) +
   ' ELSE ' + str(@Code3) + ' END'

Note: I used str(@Code1) since it was declared as int

then you can execute the statement normally

Exec sp_executesql @SQLQUERY

Hope this will help you

edited Sep 15 '16 at 09:10

answered Sep 15 '16 at 08:55

Monah

6,714
6
22
52

Hi, now it gave me the error unknown object type 'TABLEtable_nameADDColumn_Name' used in a create, drop or alter statement. Any hint what that might be? – user3052850 Sep 15 '16 at 09:08
you should put the correct Table name and also i will edit the answer to add space before the _ADD_ – Monah Sep 15 '16 at 09:10
I updated the answer by adding the proper spacing, but you need to use the correct table_name which exists – Monah Sep 15 '16 at 09:11

shing · Answer 2 · 2016-09-15T09:04:42.727

0

take a look at sp_executesql

You need to build the query as string.

I have noticed few problems on your query:

FirstColumn and SecondColumn are not declared column
When you want to add new column, you need to specify the datatype for the column. please see this

ALTER TABLE Tablecolumn ALTER COLUMN NewColumn NVARCHAR(20) NOT NULL;

for the conditions for @Code1, @Code2 and @Code3. You want this as default values? What are you trying to achieve?

edited Sep 15 '16 at 09:04

answered Sep 15 '16 at 08:57

shing

36
5

It's going to be a computed column, which are going to get a certain code depending on the condition. The codes are going to be dynamic, so that's going to change later. However, I just want to get the first part to work first. – user3052850 Sep 15 '16 at 09:10

score 0 · Answer 3 · answered Sep 15 '16 at 10:34

0

The script finally worked accordingly to this:

DECLARE @Code1 int = 1
DECLARE @Code2 int = 2
DECLARE @Code3 int = 3
DECLARE @SQLQUERY NVARCHAR (max)

SET @SQLQUERY = N'ALTER TABLE Table ADD Column 
AS CASE WHEN FirstColumn IS NULL THEN' STR(@Code1) +
    'WHEN SecondColumn IS NULL THEN' STR(@Code2) +
' ELSE' str(@Code3) + ' END'

Exec sp_executesql @SQLQUERY

answered Sep 15 '16 at 10:34

user3052850

178
3
17

The lack of spaces in `THEN'`, `'WHEN` and `ELSE'` will be problematic. The best practice when assembling object names into dynamic SQL statements is to use [`QuoteName()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. – HABO Sep 21 '20 at 18:26

sp_executesql: Add column with case statement

3 Answers3