To count the Non Null Fields

The following SQL User defined function is used to count the number of non null fields.

/* This function takes four arguments as seen below. It will return a count of the */
/* the number of these fields that are NOT null. */
/* Example: FuncFieldNotNull(NULL, Material, , NUll) = 2 */
/* FuncFieldNotNull(Purchase, Sales, MRP, Material) = 4 */



[CODE]

CREATE FUNCTION dbo.FuncFieldNotNull (@PrefixPurchase varchar(50), @ProductGroup varchar(50), @PriceGroup varchar(50), @Material varchar(50))
RETURNS Integer
AS

BEGIN

Declare @Return as Integer

Set @Return = 0

If @PrefixPurchase <> Null
Begin
Set @Return = @Return + 1
End
If @ProductGroup <>Null
Begin
Set @Return = @Return + 1
End
If @PriceGroup <>Null
Begin
Set @Return = @Return + 1
End
If @Material <>Null
Begin
Set @Return = @Return + 1
End
Return(@Return)
END

[/CODE]

You can change the numnber of fields according to your needs.