StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL: If var = '' do nothing else add a condition to a WHERE clause
text
Body
copied!<p>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.</p> <p>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.</p> <pre><code>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) </code></pre> <p>I have tried writing the last line like this(see code below) but it doesn't work.</p> <pre><code>AND CASE WHEN @itemId = '' THEN 1 ELSE (Products.ProductId = @itemId OR Sundries.SundryId = @itemId OR FreightMethods.FreightMethodId = @itemId OR SalesOrderItems.FreeTextItem = @itemId) END </code></pre> <p>Does anyone have any ideas where I am going wrong?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload