时间戳

首页 > 解决方案 > 变量为空时选择空数据 SQL Server

sql-server - 变量为空时选择空数据 SQL Server

问题描述

我想将一个变量传递给 SQL Server 中的存储过程，当它为空时，它应该返回所有为空的行。

我找不到任何对我有帮助的东西。

如果我将 null 传递给变量，它不会返回任何内容，因为您需要说IS NULL

DECLARE @itemId INT = 5435
DECLARE @id INT = NULL

SELECT 
    rt.Id,
    'Received' as Type,
    rt.Quantity,
    rt.CuttingInstuctionId,
    rt.Date
FROM 
    ReceivingItemsTransactions rt
LEFT JOIN  
    Order ci ON rt.OrderId = ci.Id
WHERE
    rt.ItemId = @itemId 
    AND ci.PlannedProductionId = @id

我希望在这种情况下只得到空行。

有什么替代方法？

标签： sql-servertsql

解决方案

使用 OR 条件来测试两者

declare @itemId int = 5435
declare @plannedProdId int = null

SELECT rt.Id,
'Received' as Type,
rt.Quantity,
rt.CuttingInstuctionId,
rt.Date
FROM ReceivingItemsTransactions rt
left join CuttingInstructions ci
on rt.CuttingInstuctionId = ci.Id
where rt.ItemId = @itemId and 
(
   (ci.PlannedProductionId = @plannedProdId) 
OR (@plannedProdId IS NULL AND ci.PlannedProductionId IS NULL
)

或者你可以使用工会？

declare @itemId int = 5435
declare @plannedProdId int = null

SELECT rt.Id,
'Received' as Type,
rt.Quantity,
rt.CuttingInstuctionId,
rt.Date
FROM ReceivingItemsTransactions rt
left join CuttingInstructions ci
on rt.CuttingInstuctionId = ci.Id
where rt.ItemId = @itemId and 
(ci.PlannedProductionId = @plannedProdId) 
UNION ALL
SELECT rt.Id,
'Received' as Type,
rt.Quantity,
rt.CuttingInstuctionId,
rt.Date
FROM ReceivingItemsTransactions rt
left join CuttingInstructions ci
on rt.CuttingInstuctionId = ci.Id
where rt.ItemId = @itemId and (@plannedProdId IS NULL AND ci.PlannedProductionId IS NULL)

推荐阅读