CODEWITHSUNDEEP
sqloracle-databaseplsql
Victoria Gurina
Victoria Gurina

Reputation: 3

how to check if all the columns in the input table are empty in SQL

I have a stored procedure that takes an array of rows as one of the parameters. I want to check all the fields of a row and if all of them empty except line_id, do not insert that row in the oracle database. In java or C#, that condition I would right like that (!(a==c==f=="")). How can I do it in SQL? I tried this: if not (a = c = f and f is null) then insert ...

UPD: I do not need to create a new procedure, I only want to check fields of one of the INPUT parameters that is a table.

UPD2: Actually, my question more about the syntax of similar condition clause in SQL

THE ANSWER: if not (COALESCE(a,c,f) is null) then insert...

Thank you, @Conrad Frix.

Upvotes: 0

Views: 195

Answers (2)

Himanshu
Himanshu

Reputation: 3970

Is this what you want? 

      Create procedure checkdata  
      As 
      IIF(Select count(*) from table where
            A Is NULL and B is Null and C is 
            Null and LineId Is not 
            null  >1)
      Then
      ..... 
      Else 
      Insert into table values as Select * 
      from table

      End IF;
       End;

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52645

You could use Coalesce. E.g.

E.g.

Insert into table1 (...) Select .... From table2 Where COALECE(a,b,c) is not null

Upvotes: 2

Related Questions