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
– HoneyBadger, Commented Feb 6, 2019 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
– Thom A ♦, Commented Feb 6, 2019 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 Arshad
– Salman Arshad, Commented Feb 6, 2019 at 13:39
Possible duplicate of How to check if a column exists in a SQL Server table? — Lajos Arpad
– Lajos Arpad, Commented Feb 6, 2019 at 13:43

Lajos Arpad · Accepted Answer · 2019-02-06 13:54:45Z

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 6, 2019 at 13:54

Lajos Arpad

80.4k42 gold badges123 silver badges234 bronze badges

answered Feb 6, 2019 at 13:41

Sauer

856 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ilyes · Accepted Answer · 2019-02-06 13:53:12Z

0

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

answered Feb 6, 2019 at 13:53

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Comments

DDS · Accepted Answer · 2019-02-06 14:35:37Z

0

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

edited Feb 6, 2019 at 14:35

answered Feb 6, 2019 at 13:59

DDS

2,48019 silver badges44 bronze badges

Collectives™ on Stack Overflow

SQL if column is empty, add an empty column

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related