How to check IS NULL in dynamic query in sql server

Question

I did following store procedure using dynamic query, see the following blueprint of code

ALTER PROCEDURE [dbo].[usp_Report] (
 @LocationId INT = NULL
,@UserId INT = NULL)

DECLARE @miscquery NVARCHAR (MAX); begin SET @miscquery='

                       SELECT 
                        ,A.AgreementNumber AS Contract
                        ,A.AgreementId
                        
                    FROM tblAgreement A
                    
                    WHERE 
                    AND (A.IsDeleted = 0 or A.IsDeleted is null)
                    AND (
                                (
                                    ' + convert(NVARCHAR(30), @LocationId) + ' IS NULL  
                                    AND (
                                    A.CheckoutLocation IN (
                                        SELECT LocationId
                                        FROM [dbo].[tblUserLocations]
                                        WHERE UserID = ' + convert(VARCHAR(10), @userId) +' 
                                            AND IsDeleted = 0
                                        )
                                    OR A.CheckoutLocation IS NULL
                                    )
                                )
                                OR A.CheckoutLocation =   ' + convert(VARCHAR(10), @LocationId) +' 
                        )'
    
                    
    
                EXEC (@miscquery)
end
)

here when i execute the query with @Locationid is null, results doesn't return table, it returns like following

(63 rows affected)

(2325 rows affected)

please help me. thank you

Answer 1

The code you have there cannot be your actual code, firstly because right at the start you try to set a variable called @miscquery before you declare it. There's also no reason for this code to be dynamic, so it's clear you're doing some other stuff as well.

I will take it as a given that for some reason you "need" dynamic SQL. I will put in the standard warning about sanitising your inputs. That was it.

OK. Even if @miscquery had been declared, the code as written will not produce any results. It will either throw a syntax error, or do nothing, depending on your setting for concat_null_yields_null.

Let's take the likely case: you have the default setting for this, which means that when you concatenate a varchar to null, the result is null.

Observe the following code:

declare @locationId int = null;
select 'this sentence ends with...' + convert(nvarchar(30), @locationId);

What will be the output?

1. "This sentence ends with... null"
2. "This sentence ends with..."
3. null

The answer is 3. And notice: that's not a string with the value "null". That's just null. When you convert the null value to a string, you don't get a string with the value "null", you get the null value.

OK, so now we try to add null to the end of our string. When you try to concatenate the null value to a string with the + operator, the entire result is null.

Therefore, your entire @miscquery variable is null at the end of the assignment.

So what you are then doing is the same as this:

declare @myquery varchar(max) = null;
exec sp_executesql @myquery

Which is valid, and doesn't throw any error, but does nothing. Then the rest of your code does whatever it does, and produces the results you see.

if concat_null_yields_null was set to off then you would get a syntax error, because the resulting text would not be valid SQL when you handed it to sp_executesql.

As Dan said, the best and safest solution would be to parameterize your inputs. But putting that aside, the solution you need would look something like the following. In the iif() function, I look at the value of @locationId. If it is null, then I want the string "null". If it is not null, then I want the string conversion of whatever value it has.

declare @locationId int = null;

declare @query varchar(max) = 
   'select * from mytable where ' 
    + iif(@locationId is null, 'null', convert(nvarchar(30), @locationId)) 
    + ' is null';

print @query;