sql参数可以是可选的

Question

我遇到这个问题，我需要为我的存储过程设置“可选”参数才能正常工作。 例如，我有这个：

CREATE PROCEDURE [dbo].[Search]
(
 @StartTime datetime = NULL,
 @EndTime datetime = NULL,
 @CustomerEmail nvarchar(255) = NULL,
 @OrderStatusID int
)

现在，在我的 .net 网站中，我有一个这样的示例，请记住，只能有一个参数，也可能有全部参数：

commAdvanced.Parameters.Add("@StartTime", SqlDbType.DateTime).Value = startDate;
commAdvanced.Parameters.Add("@EndTime", SqlDbType.DateTime).Value = endDate;
commAdvanced.Parameters.Add("@CustomerEmail", SqlDbType.nvarchar).Value = null;
commAdvanced.Parameters.Add("@OrderStatusID", SqlDbType.Int).Value = null;

这是查询：

SELECT * FROM Order 
WHERE CreatedOn > CAST(@StartTime as datetime) 
  AND CreatedOn < CAST(@EndTime as datetime)
  AND Order.OrderStatusID = @OrderStatusID 
  AND Order.CustomerEmail = @PaymentStatusID

我没有得到当我这样做时记录，有人可以帮我我需要改变什么吗？

Answer 1

请注意：可维护性的最佳解决方案可能不利于性能和可扩展性（请注意，自 SQL Server 2008 以来，竞争环境已经发生了变化）。

这是一个相当大的主题，我建议您阅读 T-SQL 中的动态搜索条件厄兰·索马斯科格。

Answer 2

假设选择查询中存在拼写错误，可以尝试

AND Order.CustomerEmail = ISNULL(@CustomerEmail, CustomerEmail)
并且 OrderStatusID = ISNULL(@OrderStatusID, OrderStatusID)

另外，您不需要将 @StartTime 等强制转换为日期时间。他们已经属于那种类型了，不是吗？

Answer 3

根据您的问题，4 个参数中的一个或所有参数都可以传递给您的过程。并且您的查询已在 where 子句中检查了所有列。因此，如果我理解正确的话，目前，在所有 4 个参数都传递了有效数据之前，您将无法获得任何记录。

试试这个，我只是构造一个查询然后执行它。我检查每个参数是否为空，并且只有非空的参数值才会包含在 where 子句中。

declare @sqlstring varchar(1000)

set @sqlstring = 'SELECT * FROM Order WHERE 1=1 '

if @StartTime <> null OR @StartTime <> '' 
BEGIN 
set @sqlstring = @sqlstring + 'AND CreatedOn > CAST(@StartTime as datetime) ' 
END 

if @EndTime <> null OR @EndTime <> '' 
BEGIN 
set @sqlstring = @sqlstring + 'AND CreatedOn < CAST(@EndTime as datetime) ' 
END 

if @OrderStatusID <> null OR @OrderStatusID <> '' 
BEGIN 
set @sqlstring = @sqlstring + 'AND OrderStatusID = @OrderStatusID ' 
END 

if @CustomerEmail <> null OR @CustomerEmail <> '' 
BEGIN 
set @sqlstring = @sqlstring + 'AND CustomerEmail > @CustomerEmail ' 
END 

print @sqlstring
Exec(@sqlstring)

Answer 4

您需要为那些想要保留 NULL 的参数传入 DBNull.Value （不仅仅是 .NET null）：

commAdvanced.Parameters.Add("@CustomerEmail", SqlDbType.nvarchar).Value = DBNull.Value;
commAdvanced.Parameters.Add("@OrderStatusID", SqlDbType.Int).Value = DBNull.Value;

这应该可以解决问题。

另外：如果您指定 varchar 或 nvarchar 类型的参数，我建议始终指定其长度。

Answer 5

我认为这很简单，因为您的查询不正确。

让我们以仅提供@StartTime 为例。您的查询将计算为：

SELECT * FROM Order  
WHERE CreatedOn > CAST(@StartTime as datetime)  
  AND CreatedOn < null
  AND Order.OrderStatusID = null
  AND Order.CustomerEmail = null

假设 ANSI NULL 为 ON，则与 null 相比没有值返回 true 结果，因此您的结果集为空。

我认为 Noel 的答案最接近 - 我建议：

SELECT * FROM Order  
WHERE (@StartTime is null or CreatedOn > @StartTime)
  AND (@EndTime is null or CreatedOn < @EndTime)
  AND Order.OrderStatusID = isnull(@OrderStatusID, Order.OrderStatusID)
  AND Order.CustomerEmail = isnull(@CustomerEmail, Order.CustomerEmail)

marc_s 也是正确的 - 如果您明确想要将 @ 参数值设置为 SQL null，请将其设置为

commAdvanced.Parameters.Add("@CustomerEmail", SqlDbType.nvarchar).Value = DBNull.Value;          
commAdvanced.Parameters.Add("@OrderStatusID", SqlDbType.Int).Value = DBNull.Value;   

但是，因为您在存储过程中提供了默认值 null （除了 @OrderStatusID - 拼写错误？），您实际上根本不需要将这些参数添加到命令中。