SQL if column is empty, add an empty column

Question

I want to be able to check if we have a column and if not, then we just want to add an empty column,

    IF Users.[parties] = '' OR NULL
        BEGIN
            SELECT [parties] 
            FROM Users 
            UNION 
            SELECT 'Empty'
        END

The Users.[parties], we check to see if we have a column but if we don't, it will result in a crash, in the case for this event I thought it would be best just to add an empty column with the name of Empty but I can't get the code to work above.

If we do have columns, the results will be something like...

ColumnsName    ColumnAge
data             33
data             22

But when there isn't a column, it crashes and ideally I would like it to just have an empty column like this,

EmptyColumn

I don't follow the problem and what you are trying to do, but `union` adds row(s), not columns. — HoneyBadger, Feb 06 '19 at 13:31
`Users.Parties` can't even be referenced in that `IF`, it's not within a subquery so the server won't know the context of a what it is. — Thom A, Feb 06 '19 at 13:35
So your query should return a 2col x Nrow result if there are n rows and 1col x 1row if there are none? — Salman A, Feb 06 '19 at 13:39
Possible duplicate of [How to check if a column exists in a SQL Server table?](https://stackoverflow.com/questions/133031/how-to-check-if-a-column-exists-in-a-sql-server-table) — Lajos Arpad, Feb 06 '19 at 13:43

score 1 · Answer 1 · edited Feb 06 '19 at 13:54

1

The code below checks whether a column exists in the table, in our case the name of the column is columnName and the name of the table is tableName.

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
    BEGIN
        -- Column exists
       SELECT [parties] FROM Users
    END
ELSE 
    BEGIN
        -- Column does not exists
        SELECT 'Empty'[parties]
    END

edited Feb 06 '19 at 13:54

Lajos Arpad

64,414
37
100
175

answered Feb 06 '19 at 13:41

Sauer

85
6

score 0 · Answer 2 · answered Feb 06 '19 at 13:53

I think you just want

IF EXISTS(
           SELECT 1 
           FROM Sys.Columns 
           WHERE Name = N'parties'
                 AND 
                 Object_ID = Object_ID(N'SchemaName.Users')
         )
BEGIN
  SELECT parties
  FROM Users;
END
ELSE
BEGIN
  SELECT 'EmptyColumn' EmptyColumn -- or NULL EmptyColumn 
  FROM Users; 
END

DDS · Answer 3 · 2019-02-06T14:35:37.320

I'll try with this: (I'm not sure it works)

select case when ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) =0 --count rows
          then 'empty'  -- if 0 output empty
          else parties end as parties --else ouputs the result
from your_table

This is a more 'standard' approach

CREATE VIEW user_filled as
SELECT [parties] 
FROM Users 
UNION 
SELECT 'EMPTY'

and when you query it (if needed -> on count(*))

select count(*) 
from user_filled
where parties <> 'EMPTY'

on join

select *
from user_filled join other_table 
      on (user_filled <> 'EMPTY and userfilled.key= other_table.key)

NOTE: put the clause into the ON so it's filtered out BEFORE the join is made

SQL if column is empty, add an empty column

3 Answers3