Saturday, September 30, 2017

Return NULL value from empty table

You may come at the situation where you need to return NULL even if your table is completely empty. For example, you need to make an action depend on if the table's column has value or not., it's generally the case, when you would like to load the data in your data warehouse for the first time and then extract delta every day.

Fig 1: Make action depend on the tab'e's data

From the above case, we need to check if a column of the table has value or not. We have created a table which is empty.

Fig 2: Empty Data set

Now if you would like to produce a value when there is no data in a table, you can return NULL.

There maybe many ways to find out , but my preferable way will be

Approach 1:

select case when count(1)=0 then NULL else 1 end from dbo.[TimeStamp]

In case you need to pass parameter then:

select case when count(1)=0 then NULL else 1 end from dbo.[TimeStamp] where TableName='Table1'

Approach 2:

select
  (select [MaxTimeStamp] from dbo.[TimeStamp] ) as [MaxTimeStamp]

In case you need to pass parameter then:

  select
  (select [MaxTimeStamp] from dbo.[TimeStamp] where tablename='table1') as [MaxTimeStamp]


You can use any one of the syntax to return NULL whenever you need it.