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?

Devart · Accepted Answer · 2013-12-30 08:21:46Z

3

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

answered Dec 30, 2013 at 8:21

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adriaan Stander · Accepted Answer · 2013-12-30 08:23:01Z

3

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.

answered Dec 30, 2013 at 8:23

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Collectives™ on Stack Overflow

Creating optional WHERE clause inside of SQL Server stored procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related