Wanted:Function that returns Null or its argument for 0/1

1

In my web application I have a query with a control parameter that enables switching between two different reports: one that groups by 'Section' and the other that groups by 'Section' and 'Date'
I'm doing this trick:

declare @switch nvarchar(1)
set @switch = CASE WHEN @checkbox = 1 THEN '' ELSE NULL END

SELECT SUM(minutes) as Time, Section, COALESCE(@switch,Date) as Date
FROM Table
GROUP by Section, COALESCE(@switch,Date)

That works, but has the ugly side effect that the empty date is then displayed as '1900-01-01' (which I will hide on the webside). I'm puzzeling over a function that returns a column or Null based on the value (1/0) of the control parameter.

Someone has a better solution?
Martin

2

Try to integrate the check/switch in your query like this:

DECLARE @TABLE AS TABLE
(
    [minutes] INT NOT NULL
    ,[Section] INT NOT NULL
    ,[Date] DATETIME NOT NULL
)
INSERT INTO @TABLE(minutes,Section,Date)
VALUES (10,1,'20161011')
    ,(12,1,'20161012')
    ,(5,2,'20161011')

declare @switch nvarchar(1)
     ,@checkbox tinyint = 0

SELECT 
    SUM(minutes) as Time
    , Section
    , CASE WHEN @checkbox = 1 THEN NULL ELSE Date END as Date
FROM 
    @TABLE
GROUP by 
    Section
    , CASE WHEN @checkbox = 1 THEN NULL ELSE Date END

In your place , I will create 2 stored procedures:

  • first - call Total_MinutesSections - that is a total without dates , with the body like this:

     SELECT 
     SUM(minutes) as Time  
   , Section    
 FROM 
     TABLE
 GROUP by 
     Section

  • second - call Total_MinutesSectionsDates - that is a total with dates , with the body like this:

      SELECT 
      SUM(minutes) as Time
      , Section
      , Date
  FROM 
      TABLE
  GROUP by 
      Section
      , Date

and base on your checkbox you;re calling the right one.

3

+1 Absolutely this. Definitely avoid using a User Defined Function for this (if that was your intention) as the performance will be dreadful. You won't notice how dreadful until the APP scales up - by which time you'll probably have the function all over your code and a Month-of-Sundays' work to replace it :frowning:

Not needed in @stepson solution, but if you encounter this issue you can work around it with:

SELECT
...
    NullIf(COALESCE(@switch, Date), '19000101') as Date
...

which will give you NULL instead of the CompareValue matched by the NullIf(Value, CompareValue) function. If you want to force a different value, instead of the CompareValue, you can do

COALESCE(NullIf(MyColumn, @CompareValue), @ReplaceValue)

to force a different @ReplaceValue value when MyColumn matches @CompareValue

Bit unwieldy, but runs very efficiently.

1 Like
4 
SELECT SUM(minutes) as Time, Section, 
    NULLIF(COALESCE(@switch,Date),'19000101') as Date
FROM Table
GROUP BY Section, NULLIF(COALESCE(@switch,Date),'19000101')
1 Like
5

Thank you, I will use the solution proposed by Kristen and ScottPletcher as it can adapt to different column types (INT, VARCHAR).

@stepson: Actually I'm using this trick quite often and that's why I don't like the idea of writing a stored procedure each time.
Years ago I read an article that explained that one should try to avoid the CASE-WHEN expression whenever it is possible to substitute it with the SQL functions Coalesce, Nullif, IsNull. Performance will improve - hope that this isn't a legend.

@Kristen: I didn't looked for a user defined function but for a smart combination of SQL functions like the one you posted.

Martin

6

SELECT CHOOSE(@checkbox, Date) As [Date]

Assuming @checkbox will be either 0 or 1 - when the value is 1 it will return the value from the column and when the value is 0 it will return a NULL.

1 Like
7

@jeffw8713: Very handy! One should spend more time to explore all new features of the newer SQL versions; this one came up with 2012 as I just read.