T-SQL multiple WHERE clause if the string is empty

Question

I have three fields I want to filter my query by. I want to query even if 1 or 2 of the fields are empty (passing an empty string). I can figure out 1 field, but once I add the other two, I do not get any results. Curly brackets are my form fields that get passed to the query.

IF '{Envelope Size}' <> ''
SELECT Tools.ToolNo, 
       Tools.[Name]
FROM Tools
     LEFT JOIN
(
    SELECT AdditionalInfo.OwnerID, 
           AdditionalInfo.UserDefined3, 
           AdditionalInfo.UserDefined4, 
           AdditionalInfo.UserDefined5, 
           AdditionalInfo.UserDefined10, 
           AdditionalInfo.UserDefined24
    FROM AdditionalInfo
    WHERE AdditionalInfo.ModuleID = 35
) AS EnvStyle ON Tools.ToolID = EnvStyle.OwnerID
WHERE EnvStyle.UserDefined24 LIKE '{Envelope Size}';
ELSE
SELECT Tools.ToolNo, 
       Tools.[Name]
FROM Tools
     LEFT JOIN
(
    SELECT AdditionalInfo.OwnerID, 
           AdditionalInfo.UserDefined3, 
           AdditionalInfo.UserDefined4, 
           AdditionalInfo.UserDefined5, 
           AdditionalInfo.UserDefined12
    FROM AdditionalInfo
    WHERE AdditionalInfo.ModuleID = 35
) AS EnvStyle ON Tools.ToolID = EnvStyle.OwnerID
WHERE EnvStyle.UserDefined3 <> 'Stationery'
      AND EnvStyle.UserDefined4 = (CASE WHEN '{Env. Height}' = '' THEN 'NULL' ELSE '{Env. Height}' END)
      AND EnvStyle.UserDefined5 = (CASE WHEN '{Env. Width}' = '' THEN 'NULL' ELSE '{Env. Width}' END)
      AND EnvStyle.UserDefined12 = (CASE WHEN '{Flap Size}' = '' THEN 'NULL' ELSE '{Flap Size}' END);

CASE WHEN '{Env. Height}' = '' The literal string '{Env. Height}' is never going to equal the literal string ''. — Thom A
– Thom A ♦, Commented Apr 13, 2021 at 15:44

WAMLeslie · Accepted Answer · 2021-04-13 16:14:51Z

1

I'm not exactly clear on what your data structure is and the curly braces are throwing me off a bit, but maybe something like this might work for you. You could change your filter conditions to use an OR with a CASE to force a match in those cases when the values are blank. Again, I'm not sure of your structure and the below is just following your example, but hopefully this might get you in the right direction.

IF '{Envelope Size}' <> ''
SELECT Tools.ToolNo, 
       Tools.[Name]
FROM Tools
     LEFT JOIN
(
    SELECT AdditionalInfo.OwnerID, 
           AdditionalInfo.UserDefined3, 
           AdditionalInfo.UserDefined4, 
           AdditionalInfo.UserDefined5, 
           AdditionalInfo.UserDefined10, 
           AdditionalInfo.UserDefined24
    FROM AdditionalInfo
    WHERE AdditionalInfo.ModuleID = 35
) AS EnvStyle ON Tools.ToolID = EnvStyle.OwnerID
WHERE EnvStyle.UserDefined24 LIKE '{Envelope Size}';
ELSE
SELECT Tools.ToolNo, 
       Tools.[Name]
FROM Tools
     LEFT JOIN
(
    SELECT AdditionalInfo.OwnerID, 
           AdditionalInfo.UserDefined3, 
           AdditionalInfo.UserDefined4, 
           AdditionalInfo.UserDefined5, 
           AdditionalInfo.UserDefined12
    FROM AdditionalInfo
    WHERE AdditionalInfo.ModuleID = 35
) AS EnvStyle ON Tools.ToolID = EnvStyle.OwnerID
WHERE EnvStyle.UserDefined3 <> 'Stationery'
      AND (EnvStyle.UserDefined4 = {Env.Height} 
            OR 1 = CASE WHEN {Env.Height} = '' THEN 1 ELSE 0 END)
      AND (EnvStyle.UserDefined5 = {Env.Width} 
            OR 1 = CASE WHEN {Env.Width} = '' THEN 1 ELSE 0 END)
      AND (EnvStyle.UserDefined12 = {Flap Size} 
            OR 1 = CASE WHEN {Flap Size} = '' THEN 1 ELSE 0 END);

answered Apr 13, 2021 at 16:14

WAMLeslie

1,2711 gold badge7 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Tuma Over a year ago

Thank you @WAMLeslie this is perfect. It is working just like I wanted! I Had to fix the syntax errors, but you would not have known. The curly brackets are the API to get the string from the form field. Can you please explain the logic??

Michael Tuma Over a year ago

Here it is with the corrected syntax.

WHERE EnvStyle.UserDefined3 <> 'Stationery'       'AND (EnvStyle.UserDefined4 = '{Env. Height}' '             OR 1 = CASE WHEN '{Env. Height}' = '' THEN 1 ELSE 0 END)       AND (EnvStyle.UserDefined5 = '{Env. Width}'              OR 1 = CASE WHEN '{Env. Width}' = '' THEN 1 ELSE 0 END)       AND (EnvStyle.UserDefined12 = '{Flap Size}'              OR 1 = CASE WHEN '{Flap Size}' = '' THEN 1 ELSE 0 END);

WAMLeslie Over a year ago

For each of the three conditions, the OR allows you to match on either the exact value being equal OR, in the case where the value is blank, the CASE forces a 1=1 condition which is always true.

Collectives™ on Stack Overflow

T-SQL multiple WHERE clause if the string is empty

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related