sql-server - 在 SQL Server 存储过程中创建可选的 WHERE 子句

Question

我写了这个 SQL Server 2008 存储过程

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

现在的问题是输入变量 @userid、@gaugesize、@datefrom 和 @dateto 如果用户未输入任何值，则可能会收到空值。在那种情况下，我想返回没有 where 条件部分的整个结果。

有人可以建议我如何在 SQL Server 中执行此操作吗？

Answer 1

可能这对你有帮助-

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