Returning NULL data values while using an SP_ExecuteSQL

Question

I'm using an sp_executesql so I can be flexible with what I choose to return. I'm encountering a problem when attempted to return data where there is none.

Say for instance I'm attempting to find all rows where there is no owner assigned. Currently, my query is written as follows.

 if @owner <> ''
    Begin
        SELECT @sql = @sql + 'and owner LIKE ''%'+@owner+'%'''
    END

This allows for me to not have to select an owner. If I do, it will of course be included in my query.

Since I'm now attempting to add a line that would basically allow me to just bring back unassigned owners:

 if @owner = 'Unassigned'
BEGIN
    SELECT @sql = @sql + 'and owner IS NULL' 
END

However, when I'm doing this, it will bring back the entire list. Thoughts / suggestions?

Could you print out @sql (instead of executing it) and post the result? Even if you have to change the table names & field names? — Kenneth Fisher, Mar 26 '13 at 15:15
I took out a lot of the other script that was unnecessary, but there are more IFs other than the two I posted. I did it this way for aesthetic purposes. — , Mar 26 '13 at 15:22
Are you searching for if owner = 'Unassigned' .... ELSE if owner <> '' .... (i had to remove @ because comment allows only one) — bummi, Mar 26 '13 at 15:23
May be its entering in both conditions...owner = unassigned and owner is <> '' ???? and you have select owner = 'unassigned' and if owner = 'Unassigned' ....they are differents capitalized and non capitalized — Hackerman, Mar 26 '13 at 15:24
I believe it is. I think the issue ends up being how it reads the NULL value when the variable is 'Unassigned' — , Mar 26 '13 at 15:32
So when it returns the list for your `if @owner = 'Unassigned'...` it returns everything? You see both Null and non-null values for owner returned? — Eric J. Price, Mar 26 '13 at 18:05

score 3 · Answer 1 · answered Mar 26 '13 at 15:31

3

IF you change your last line to PRINT @sql you will notice both conditions are there. Try changing your code to this.

if @owner <> ''
Begin
    if @owner = 'Unassigned'
        SELECT @sql = @sql + 'and owner IS NULL'
    else
        SELECT @sql = @sql + 'and owner LIKE ''%'+@owner+'%'''
END

answered Mar 26 '13 at 15:31

Kenneth Fisher

3,692
19
21

You were exactly right. I had written them as two different IF statements, whereas instead I should have done the above. Thanks very much! – Mar 26 '13 at 15:39

score 0 · Answer 2 · answered Mar 26 '13 at 17:16

Also you can use option with CASE expression

SELECT @sql = @sql + CASE ISNULL(@owner, '') 
                       WHEN '' THEN ''
                       WHEN 'Unassigned' THEN ' and owner IS NULL'
                       ELSE ' and owner LIKE ''%' + @owner + '%''' END

Returning NULL data values while using an SP_ExecuteSQL

2 Answers2