CODEWITHSUNDEEP
sql-servert-sql
barryDev
barryDev

Reputation: 119

Select null data when variable is null SQL Server

I want to pass in a variable into a stored procedure in SQL Server when its null it should return all the rows that are null.

I couldn't find anything that helped me.

If I pass in the null into variable it returns nothing because you need to say 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

I expect to get only the null rows in this scenario.

What would be an alternative to this?

Upvotes: 2

Views: 75

Answers (1)

Milney
Milney

Reputation: 6417

Use an OR condition to test for both

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
)

Or you could use a union maybe?

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)

Upvotes: 1

Related Questions