Handling IS NULL inside string in SQL

Question

I have a SQL query which is written in string and then executed using command Exec(string) like the following :

Declare @TestId bigint = null
Declare @Query nvarchar(max)
set @Query = 'SELECT * from Registrations where RegistrationId = 15 AND (' + CAST(@TestId AS NVARCHAR) + ' IS NULL OR TestId = ' + CAST(@TestId AS NVARCHAR) + ') '
EXEC(@Query)

The problem now that the IS NULL is not parsed correctly inside the string but when i remove the IS NULL from the string it works correctly and when the @TestId takes a value other than null it works correctly where the problem now is in the casting of the IS NULL inside the @Query string.

Note : @TestId is a procedure parameter

I need to know how to make the SQL feel with the IS NULL and parse it correctly

Thanks in advance

Answer 1

Building dynamic queries is bad, m'kay...that said, you can fix it by moving the null check outside of the dynamic query and handling it there:

set @Query = 'select * from registrations where registrationId = 15 and
             CAST(TestId AS NVARCHAR) = ' + ISNULL(CAST(@TestId AS NVARCHAR), '')

On another note, why are you using a dynamically built query for something as simple as this? It could be a regular SELECT statement which would make things much less complicated.

select * from registrations where registrationId = 15 and TestId = @TestId

Answer 2

From your code I suspect what you want to is to filter the results from Registrations by the TestId, given that it is not null. Why not do this programatically instead of in the SQL query? Something along those lines (syntax my be wrong):

Declare @TestId bigint = null
Declare @Query nvarchar(max)
if(@TestId IS NULL)  
   set @Query = 'SELECT * from Registrations where RegistrationId = 15'
else
   set @Query = 'SELECT * from Registrations where RegistrationId = 15 AND (TestId = ' + CAST(@TestId AS NVARCHAR) + ') '
EXEC(@Query)

Also, Justin Niessner is right, dynamic SQL is to be avoided if possible (this could be done without dynamic SQL).

Answer 3

You don't need dynamic SQL for this. Also if you were to generate dynamic SQL one of the benefits of doing so is so that your queries do not need to have this sort of WHERE TestId =@TestId OR @TestId IS NULL construct which causes problems with plan caching and unnecessary table scans.

Declare @TestId bigint = null
Declare @Query nvarchar(max)

IF @TestId IS NULL
SELECT * from Registrations where RegistrationId = 15
ELSE
SELECT * from Registrations where RegistrationId = 15 AND TestId =@TestId

Edit

Following comment if you do need dynamic SQL then use sp_executesql and generate different strings for the case where @TestId is null. Don't shoehorn both cases into the same query.

Answer 4

If you really do need to use dynamic sql for this, use sp_executesql like this:

Declare @TestId bigint = null
Declare @Query nvarchar(max)
set @Query = 'SELECT * from Registrations where RegistrationId = 15 AND (@TestId IS NULL OR TestId = @TestId)'

EXECUTE sp_executesql @Query, N'@TestId BIG INT', @TestId

Answer 5

The other answers provide the solutions. Here is why your solution didn't work. When @TestId is null, you are concatenating a null to your @Query string, and that assigns null to @Query. If you print your @Query in place of the the exec, you will see the query that would run.

Declare @TestId bigint = 10--null
Declare @Query nvarchar(max)
set @Query = 'SELECT * from Registrations where RegistrationId = 15 AND (' + CAST(@TestId AS NVARCHAR) + ' IS NULL OR TestId = ' + CAST(@TestId AS NVARCHAR) + ') '
--EXEC(@Query)
print @Query