Creating optional WHERE clause inside of SQL Server stored procedure

Question

I have written this SQL Server 2008 stored procedure

ALTER PROCEDURE [dbo].[sp_Reports]
   @userid    int,
   @gaugesize decimal,
   @datefrom  date,
   @dateto    date
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    select 
        ROW_NUMBER() OVER(PARTITION BY actions.UserID ORDER BY actions.UserID) as 'Sr.No',
        actions.UserID, gauges.GaugeSize,
        SUM(Case When actions.actiontype='issue' then 1 else 0 end) as 'Issued',
        SUM(Case When actions.actiontype='return' then 1 else 0 end) as 'Returned'
    from 
        tblAction actions
    join 
        tblGauge gauges on actions.GaugeID = gauges.GaugeID
    where 
        actions.UserID = @userid 
        and gauges.GaugeSize = @gaugesize
        and actions.Time between @datefrom and @dateto
    group by 
        actions.UserID, gauges.GaugeSize
END

Now the problem is it is possible that the input variables @userid, @gaugesize, @datefrom and @dateto might receive nulls if user has not entered any values. In that scenario I would want to return the entire result without the where condition part.

Please can somebody suggest on how I can go about doing this in SQL Server?

Answer 1

Possible this be helpful for you -

ALTER PROCEDURE [dbo].[sp_Reports] 
    @userid INT,
    @gaugesize DECIMAL(18,2),
    @datefrom DATE,
    @dateto DATE
AS
BEGIN

    SET NOCOUNT ON;

    SELECT
        ROW_NUMBER() OVER (PARTITION BY a.UserID ORDER BY a.UserID) AS [Sr.No],
        a.UserID,
        g.GaugeSize,
        COUNT(CASE WHEN a.actiontype = 'issue' THEN 1 END) AS [Issued],
        COUNT(CASE WHEN a.actiontype = 'return' THEN 1 END) AS [Returned]
    FROM dbo.tblAction a
    JOIN dbo.tblGauge g ON a.GaugeID = g.GaugeID
    WHERE a.UserID = ISNULL(@userid, a.UserID)
        AND g.GaugeSize = ISNULL(@gaugesize, g.GaugeSize)
        AND a.TIME BETWEEN ISNULL(@datefrom, '17000101') AND ISNULL(@dateto, '30000101') 
    GROUP BY a.UserID, g.GaugeSize

END

Answer 2

You could change the WHERE clause to take into account NULLS

Something like

where   (actions.UserID=@userid  OR @userid IS NULL)
and     (gauges.GaugeSize=@gaugesize OR @gaugesize IS NULL)
and     (       actions.Time between @datefrom and @dateto
            OR  (actions.Time <= @dateto AND @datefrom IS NULL)
            OR  (actions.Time >= @datefrom AND @dateto IS NULL)
            OR  (@datefrom IS NULL and @dateto IS NULL)
        )

As you can see the between part can make for some interesting boolean logic.