Page 1 of 1

NULL in where statement

Posted: Mon 25 Mar 2019 14:09
by CodeManKS

i'm new on devConnect universal. Actually i have the task to update some rows by the statement "where b_id is NULL".

Here is my statement:

update test set b_id=:b_id where b_id is @b_id
Parameter b_id: 2
Parameter @b_id: DBNULL.value

This returns errors. How do I have to do this?

Thanks for help.

Re: NULL in where statement

Posted: Wed 27 Mar 2019 16:24
by Pinturiccio
You can use parameters in static SQL statements only in positions where the SQL syntax allows a parameter. For example, you cannot provide table names as parameters.

Therefore, parameters can send only values, not query parts.

Parameters cannot be used for building a query from parts. IS NULL or IS NOT NULL are query clauses, not values, and a parameter cannot be treated as a query clause.

Thus, you need to perform this query without a parameter in the WHERE clause and specify the IS NULL clause in the query text.

Re: NULL in where statement

Posted: Thu 28 Mar 2019 13:36
by CodeManKS
I wrote now the whole 'where statement' together and did not define and set the parameter. So it worked.
Thank you!