SQL Server存储过程可选参数，包括all if null

Question

我有这个存储过程：

ALTER PROCEDURE [dbo].[GetCalendarEvents]
    (@StartDate datetime, 
     @EndDate datetime, 
     @Location varchar(250) = null)
AS
BEGIN
    SELECT * 
    FROM Events 
    WHERE EventDate >= @StartDate 
      AND EventDate <= @EndDate 
      AND (Location IS NULL OR Location = @Location)
END

现在，我有了location参数，我想做的是，如果该参数不为null，那么将该参数包含在where子句中。如果参数为null，我想完全忽略where参数，只按开始和结束日期获取结果。

因为当我这样做的时候，例如：

EXEC GetCalendarEvents '02/02/2014', '10/10/2015', null

我没有得到任何结果，因为还有其他位置不是空的，而且位置参数是空的，我想从所有位置得到结果。

知道我该怎么解决吗？

Answer 1

ALTER PROCEDURE [dbo].[GetCalendarEvents]
( @StartDate DATETIME,
  @EndDate DATETIME,
  @Location VARCHAR(250) = NULL
)
AS
BEGIN
    SELECT  *
    FROM    events
    WHERE   EventDate >= @StartDate
            AND EventDate <= @EndDate
            AND Location = ISNULL(@Location, Location )
END

如果NULL列是可能的，那么这将起作用。

ALTER PROCEDURE [dbo].[GetCalendarEvents]
( @StartDate DATETIME,
  @EndDate DATETIME,
  @Location VARCHAR(250) = NULL
)
AS
BEGIN
    IF ( @loc IS NULL )
        BEGIN
            SELECT  *
            FROM    events
            WHERE   EventDate >= @StartDate
                    AND EventDate <= @EndDate
        END
    ELSE
        BEGIN
            SELECT  *
            FROM    events
            WHERE   EventDate >= @StartDate
                    AND EventDate <= @EndDate
                    AND Location = @Location
        END
END

由于可能存在性能问题，因此应合理避免使用"OR"条款。

Answer 2

WHERE子句中的部分应读取

AND (@Location IS NULL OR Location=@Location)

Answer 3

试试这个

 SELECT * 
    FROM Events 
    WHERE EventDate >= @StartDate 
      AND EventDate <= @EndDate 
      AND Location = Case When LEN(@Location) > 0 Then  @Location Else Location End

Answer 4

使用动态sql查询可以轻松完成。

ALTER PROCEDURE [dbo].[GetCalendarEvents]
(@StartDate datetime, 
 @EndDate datetime, 
 @Location varchar(250) = null)
AS
BEGIN
   DECLARE @SQL NVARCHAR(MAX);
   DECLARE @PARAMETER_DEFIINITION NVARCHAR(MAX);
   DECLARE @WHERE_PART NVARCHAR(MAX);
   SET @PARAMETER_DEFIINITION =' @StartDate DATETIME, @EndDate DATETIME, @Location VARCHAR(250) '
   SET @SQL ='SELECT * 
              FROM Events 
              WHERE EventDate >= @StartDate 
              AND EventDate <= @EndDate '
   IF @Location IS NOT NULL
   BEGIN
       SET @WHERE_PART = ' AND Location  = @Location '
   END    
   SET @SQL = @SQL + @WHERE_PART
   EXEC SP_EXECUTESQL @SQL, @PARAMETER_DEFIINITION, @StartDate, @EndDate, @Location
END

查询将根据参数动态创建。在这里，如果@location为null，那么它将不会添加到where部分。

如果您想了解有关编写动态查询的更多信息，请参阅本文。http://codingpulse.blogspot.com/2015/02/dynamic-sql-in-stored-procedure-part-1.html