如果参数为空，如何返回所有记录 [英] How to return all records if parameter is null

问题描述

以下是我的SP:

Alter PROCEDURE GetList 
(

@FromDate date = null,
@ToDate date = null

)
AS

Select * FROM CallList c
Where c.CallDate > @FromDate and c.CallDate < @ToDate 

如果没有通过日期过滤器，我想获取所有记录.

If there was no passed date filter, I want to get all the records.

我该怎么做?

推荐答案

几个可行的选择:

您可以将 @FromDate 和 @ToDate 设置为分别等于很早或很晚的日期，它们为 NULL.

You could set @FromDate and @ToDate to be equal to a very early or very late date respectively they're NULL.

您可以使用 sp_executesql 并根据需要构造带有参数的动态查询字符串，例如

You could use sp_executesql and construct a dynamic query string w/ parameters as needed e.g.

DECLARE @Sql NVARCHAR(MAX) = 'SELECT * FROM CallList C WHERE 1 = 1 '

IF @FromDate IS NOT NULL
BEGIN
  SET @Sql += ' AND C.CallDate > @xFromDate'
END

IF @ToDate IS NOT NULL
BEGIN
  SET @Sql += ' AND C.CallDate < @xToDate'
END

EXEC sp_executesql @Sql, N'@xFromDate DATETIME, @xToDate DATETIME', @xFromDate = @FromDate, @xToDate = @ToDate

后一种方法比到处使用 OR 的效果要好，因为包含 OR 的查询最终会得到非常糟糕的优化 - 它们可能适用于某些参数集，但通常不是一刀切.

This latter approach performs better than using ORs all over the place, as queries that include ORs invariably end up getting optimized very badly - they might work well for a certain set of parameters but are generally not one-size-fits-all.

这篇关于如果参数为空，如何返回所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！