SQL Server How to mention dynamic column value should not be null

Question

First see few screen shot.

See the second screen shot. where 2010 FYA and rest of the columns are dynamic columns. see first record where dynamic column values are NULL. now tell me how could i mention dynamic column value should not be null when fetching data.

here is code

   Set @AvgSql = @AvgSql+ 'Avg(CONVERT(decimal(20,6),['+@Period+'])) ['+@Period+'],'      

In this way i am storing dynamic column wise vaue into #TmpZacksCons temporary table.

 SET @sql='Insert Into #TmpZacksCons (Section, LineItem,Ord,      
 '+@PeriodCols+'      
 )      
 Select b.Section, b.LineItem,Max(Ord)+1 Ord,      
 '+@AvgSql+'      
  From #TmpAll_Broker_LI b       
  Group By b.Section, b.LineItem'      
 EXEC(@sql)  

this is my final query which return whole data where i need to filter out null value. null data should not come.

 SET @sql = '      
 Select XX.*,'''' scale,Isnull(AllowComma,''FALSE'') AllowComma,Isnull(AllowedDecimalPlace,''0'') AllowedDecimalPlace,      
     Isnull(AllowPercentageSign,''FALSE'') AllowPercentageSign,Isnull(CurrencySign,'''') CurrencySign,Isnull(BM_Denominator,'''') BM_Denominator      
 From       
 (      
 ---- Broker Detail      
 Select AA.Section,AA.LineItem,Csm.DisplayInCSM ,AA.BrokerCode Broker,AA.BrokerName,'''' BM_Element,'''' BM_Code,AA.Ord,AA.[Revise Date],AA.LineItemId,      
   Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],      
 '+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment      
 From tblCSM_ModelDetails Csm LEFT OUTER JOIN  (      
Select b.*,L.ID LineItemId         
  From #TmpAll_Broker_LI b      
  INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem      
     ) AA ON Csm.LineItemId=AA.LineItemId      
 WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+' AND Csm.BMID=0 AND Type !=''SHEET''      
 UNION       
 ----- Consensus      
 Select Section, b.LineItem,DisplayInCSM, '''' Broker,'''' BrokerName,'''' BM_Element,'''' BM_Code, Ord,'''' [Revise Date],L.ID LineItemID,      
   Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],      
  '+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment      
  From #TmpZacksCons b      
  INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem      
  INNER JOIN tblCSM_ModelDetails Csm ON Csm.LineItemID=L.ID      
  WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+' AND Csm.BMID=0      
 ---- Blue Metrics      
     UNION      
  Select Section, b.LineItem,DisplayInCSM,'''' Broker,'''' BrokerName,BM_Element,Code BM_Code, Ord,'''' [Revise Date],L.ID LineItemID,      
   Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],      
   '+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment      
  From #TmpBM b      
  INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem      
  INNER JOIN tblCSM_ModelDetails Csm ON Csm.BMID=b.code AND Csm.LineItemID=L.ID      
  WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+'      
  AND Ord IS NOT NULL      
 ) XX       
 Left Outer Join tblLiConfig ZZ      
  On XX.Section=ZZ.Section And XX.LineItem=ZZ.LI And ZZ.Ticker='''+@Ticker+'''      
 Order by ID,Ord,BM_Code,LineItem,BrokerName'  

Answer 1

OK... I'm sorry, but this is a bad answer. A really bad answer. It's the type of answer when you need a fix right now, and don't have time to fix the problem properly.

The true fix is to exclude the rows that have no valid values before combining them into the data set used to create your table. However, without knowing your processes/data much more intimately, I cannot do this. Instead, I have a complete hackjob.

Anyway, from what I can tell

• In the table #TmpZacksCons
• You have some rows where all the columns relating to dates are all NULL
• And you would like to exclude those
• The column names are set dynamically - they are in the variable @PeriodCols

What you can do is to delete the rows from #TmpZacksCons that have all NULLS. The problem is, of course, that those columns are dynamically created.

So, after your existing command

 SET @sql='Insert Into #TmpZacksCons (Section, LineItem,Ord,      
 '+@PeriodCols+'      
 )      
 Select b.Section, b.LineItem,Max(Ord)+1 Ord,      
 '+@AvgSql+'      
  From #TmpAll_Broker_LI b       
  Group By b.Section, b.LineItem'      
 EXEC(@sql) 

I suggest a DELETE command to remove one where all the columns are NULL.

UPDATE

(This new version based on updated knowledge - I didn't think COALESCE could return NULL)

The hackjob here is based on the fact we don't know the column names, so we cannot just test if column 1 is null, column 2 is null, etc. So what I'm doing is using a COALESCE expression to find if all columns are NULL, as it will return the first non-NULL value in the list.

SET @sql='DELETE FROM #TmpZacksCons 
          WHERE COALESCE('+@PeriodCols+',NULL) IS NULL'
EXEC (@sql)

If the COALESCE expression returns a NULL, it means all the columns have NULL values.

Note the additional NULL in the COALESCE is there in case the column list only has 1 column.

PREVIOUS VERSION - NO LONGER RELEVANT

So what I'm doing is to make a 'magic number' which represents that all columns are NULL via the COALESCE expression which finds the first non-NULL value in the list.

So, after your command above, I suggest

SET @sql='DELETE FROM #TmpZacksCons 
          WHERE COALESCE('+@PeriodCols+',-99999.9999) = -99999.9999'
EXEC (@sql)

If all the values are NULL, the COALESCE will return -99999.9999 which then triggers the delete.

The problem with the above, of course, is that if any of the actual values equal that magic number -99999.9999, it may delete that row.