Creating optional WHERE clause inside of SQL Server stored procedure

Viewed 386

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?

2 Answers
Related