CODEWITHSUNDEEP
sqlsql-server
NoobCoder
NoobCoder

Reputation: 127

SQL if column is empty, add an empty column

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

Upvotes: 1

Views: 1163

Answers (3)

DDS
DDS

Reputation: 2479

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

Upvotes: 0

Sauer
Sauer

Reputation: 85

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

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

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

Upvotes: 0

Related Questions