SQL：如果 var = ''不做任何其他事情向 WHERE 子句添加条件
我想在变量具有值的条件下执行 WHERE 子句的一部分。我尝试过以多种不同的方式编写此内容，但总是遇到错误，并且想知道是否有人可以提供帮助。
下面的代码是我当前有效的 SQL 语句，但如果我的 @itemId 为空，则不会返回任何内容。我只想减少 @itemId 作为值的结果。
DECLARE @itemId nvarchar(max)
SET @itemId = 'someId'
SELECT
SalesOrdersItems.Id,
CASE
WHEN SalesOrderItems.ItemType = 'P' THEN Products.ProductId
WHEN SalesOrderItems.ItemType = 'S' THEN Sundries.SundryId
WHEN SalesOrderItems.ItemType = 'F' THEN FreightMethods.FreightMethodId
ELSE SalesOrderItems.FreeTextItem
END AS ItemId
FROM SalesOrderItems
LEFT OUTER JOIN Products ON SalesOrderItems.Product = Products.Product
LEFT OUTER JOIN Sundries ON SalesOrderItems.Sundry = Sundries.Sundry
LEFT OUTER JOIN FreightMethods ON SalesOrderItems.FreightMethod = FreightMethods.FreightMethod
WHERE
SalesOrdersItems.area LIKE 'SE%'
AND (Products.ProductId = @itemId OR Sundries.SundryId = @itemId OR FreightMethods.FreightMethodId = @itemId OR SalesOrderItems.FreeTextItem = @itemId)
我尝试像这样编写最后一行（参见下面的代码），但它不起作用。
AND CASE
WHEN @itemId = '' THEN 1
ELSE (Products.ProductId = @itemId OR Sundries.SundryId = @itemId OR FreightMethods.FreightMethodId = @itemId OR SalesOrderItems.FreeTextItem = @itemId)
END
有人知道我哪里出错了吗？
I would like to perform one part of a WHERE clause on the condition that a variable has a value. I have tried writing this in many different ways but always hit an error and was wondering if someone could help.
The code below is my current SQL statement that works but will return nothing if my @itemId is empty. I would only like to reduce the results if the @itemId as a value.
DECLARE @itemId nvarchar(max)
SET @itemId = 'someId'
SELECT
SalesOrdersItems.Id,
CASE
WHEN SalesOrderItems.ItemType = 'P' THEN Products.ProductId
WHEN SalesOrderItems.ItemType = 'S' THEN Sundries.SundryId
WHEN SalesOrderItems.ItemType = 'F' THEN FreightMethods.FreightMethodId
ELSE SalesOrderItems.FreeTextItem
END AS ItemId
FROM SalesOrderItems
LEFT OUTER JOIN Products ON SalesOrderItems.Product = Products.Product
LEFT OUTER JOIN Sundries ON SalesOrderItems.Sundry = Sundries.Sundry
LEFT OUTER JOIN FreightMethods ON SalesOrderItems.FreightMethod = FreightMethods.FreightMethod
WHERE
SalesOrdersItems.area LIKE 'SE%'
AND (Products.ProductId = @itemId OR Sundries.SundryId = @itemId OR FreightMethods.FreightMethodId = @itemId OR SalesOrderItems.FreeTextItem = @itemId)
I have tried writing the last line like this(see code below) but it doesn't work.
AND CASE
WHEN @itemId = '' THEN 1
ELSE (Products.ProductId = @itemId OR Sundries.SundryId = @itemId OR FreightMethods.FreightMethodId = @itemId OR SalesOrderItems.FreeTextItem = @itemId)
END
Does anyone have any ideas where I am going wrong?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果我有误解，请告诉我，我假设如果您有一个空白的@itemID，您需要所有结果，否则您将限制结果。
我相信我的括号是正确的，但它们可能不均匀。希望它足够清楚地理解我的意图。
If I'm misunderstanding please let me know, I'm assuming that if you have a blank @itemID you want all results, otherwise you'll limit the results.
I believe I have the parens correct, but they might be uneven. Hopefully it is it clear enough to understand my intent.
您可能可以使用
NULLIF()You could probably use
NULLIF()根据我的理解，你应该做如下的事情。
希望这有帮助！
As per my understanding, you should do something like below.
Hope this helps!!