Selecting from where column equals a value or is null with cases (Mssql)

Question

In the query below im doing a search on a table called welds, this table has a column called Welds.Prefix which has NULL rows, rows with actual values, and rows with an empty value. I need help modifying my query below so that when the user inputs a non null and non empty value in @Prefix it will find the rows that match. And when @Prefix is blank it will find where Welds.Prefix is blank or where Welds.Prefix is null but im not sure how to go about it, I understand that I could do an update query and change the columns with null values to blank spaces but im looking for another way. I run the query below and I get an error "Incorrect syntax near the keyword 'OR'" I understand why im getting the error but im not sure how to fix it, thanks.

Note: The @Prefix will be inputted on the front end and will not be bound to just '', I just put it in the query for questions sake.

@Prefix varchar(30) = ''

select * 
from welds
WHERE Welds.Prefix = CASE WHEN @Prefix <> '' THEN @Prefix END
          OR Welds.Prefix = CASE WHEN @Prefix = '' THEN '' OR IS NULL END

EDIT: I basically want to selct rows where Weld.Prefix is null and where Weld.Prefix = ' ' when @Prefix = ' '

Follow: [`CASE WHEN COND1 THEN EXPR1 WHEN COND2 THEN [.. n] [ELSE COND] END`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15). In the code above a expression (not condition) is `'' OR IS NULL` - check the placement of keywords, *this incorrect grammar causes the syntax error*. — user2864740, Feb 12 '20 at 17:12
Does this answer your question? [How do I create a conditional WHERE clause?](https://stackoverflow.com/questions/10521677/how-do-i-create-a-conditional-where-clause) — Suraj Kumar, Feb 12 '20 at 17:15
Now, something like `..WHEN @PREFIX = '' OR @PREFIX IS NULL THEN..` might be appropriate.. didn't really read the entire question past "syntax error", which garnered a close vote as a typographical mistake :) — user2864740, Feb 12 '20 at 17:15
@user2864740 This wouldnt work because '@prefix' isnt a null value its just blank — 916ixer, Feb 12 '20 at 17:43

Gordon Linoff · Accepted Answer · 2020-02-12T18:01:22.840

0

I think this is the logic you want:

SELECT * 
FROM welds
WHERE Welds.Prefix = @sPrefix OR
      (@sPrefix = '' AND NULLIF(Welds.Prefix, '') IS NULL)

edited Feb 12 '20 at 18:01

answered Feb 12 '20 at 17:12

Gordon Linoff

1,242,037
58
646
786

This query only shows the Blank Weld.Prefix rows but not the null rows, which is the same result as the query i originally psoted if I took out "or is null' in the last line – 916ixer Feb 12 '20 at 17:40
@916ixer . . . Try it now. Without sample data and desired results, it is a little unclear what you mean by "is blank". – Gordon Linoff Feb 12 '20 at 18:01

Suraj Kumar · Answer 2 · 2020-02-12T17:20:25.427

0

You can try this

DECLARE @Prefix VARCHAR(30) = ''

SELECT *
FROM welds
WHERE (ISNULL(@Prefix, '') = '')
    OR (
        ISNULL(@Prefix, '') <> ''
        AND ISNULL(Welds.Prefix, '') = @Prefix
        )

edited Feb 12 '20 at 17:20

answered Feb 12 '20 at 17:13

Suraj Kumar

5,547
8
20
42

This query still shows me the Welds.Prefix that actually have a value, when I search for the blank Weld.Prefix rows I want to see the null rows too – 916ixer Feb 12 '20 at 17:38
In that case simply add where condition as - where Prefix = @Prefix – Suraj Kumar Feb 12 '20 at 17:53
How can I do that? Not sure how to put a where condition right before a where condition – 916ixer Feb 12 '20 at 17:59

score 0 · Answer 3 · answered Mar 15 '21 at 15:04

0

I have just across a near identical problem and solved using something like this:

DECLARE @Prefix VARCHAR(30) = '';

SELECT *
FROM Welds
WHERE (Welds.Prefix IS NULL AND @Prefix = '') 
    OR (Welds.Prefix = ISNULL(NULLIF(@Prefix, ''), Welds.Prefix));

answered Mar 15 '21 at 15:04

DNV

1

Welcome to Stack Overflow! It's great that you answered, but since there was already an accepted answer about half a year ago this question is probably redundant. – Daniel Soutar Mar 15 '21 at 15:14

Selecting from where column equals a value or is null with cases (Mssql)

3 Answers3