T-SQL openquery doesn't update on NULL value

Question

I have a Microsoft SQL Server trigger that updates a remote database with new values when the local database is updated. Everything works fine, and I tested the script and it updates fine, unless there is a null value.

The code is below:

DECLARE @TSQL nvarchar(4000);
SELECT  @TSQL = 
    'UPDATE
        OPENQUERY(TEST,''SELECT * FROM test_db WHERE id = ' + convert(VARCHAR(MAX), @id) +''')
     SET
        parent_id = ' + convert(VARCHAR(MAX), @parent_id) + ', user_id = ' + convert(VARCHAR(MAX), @user_id) + ', item_id = ' + convert(VARCHAR(MAX), @item_id) + ''
EXEC (@TSQL)

Everything works well if all the fields have values, but if one column is null, then the query doesn't update the row at all, no errors thrown. I tried to use COALESCE() to change the null variables to empty strings, and it will then update the row, but all the null columns become 0's and I want them to stay as NULL values. All the columns in both database allow null values and default to null so I'm not sure why I cannot update the database.

Any help would be nice, thanks!

Forgot to mention I was using openquery to interact with a MySql Database — Iqbal Khan, May 27 '16 at 19:10
You're connecting to MySQL *through* SQL Server? This sounds like a recipe for trouble. `NULL` values aren't strings. They need to be escaped differently. — tadman, May 27 '16 at 19:12
Output the @TSQL before you run it with a null value. validate the syntax then. My guess is you have a syntax error that somehow isn't being reported. like... set parent_ID = 'NULL', user_ID = 'Null', item_ID = 'NULL' but one of those columns may be numeric? and an implicit cast is failing? — xQbert, May 27 '16 at 19:15
It was outputting as empty when a null value was in the string, but outputted fine when it wasn't null, I just ended up using `COALESCE` to set the value to `'NULL'` — Iqbal Khan, May 27 '16 at 19:25

score 1 · Accepted Answer · edited May 27 '16 at 19:29

1

Try this. Use ISNULL and if the value is null, use 'NULL' in single quotes. When the string is concatenated together, it won't keep the quotes, so it would set it to a NULL value and not a string of 'NULL'.

DECLARE @TSQL nvarchar(4000);
SELECT  @TSQL = 
    'UPDATE
        OPENQUERY(TEST,''SELECT * FROM test_db WHERE id = ' + convert(VARCHAR(MAX), @id) +''')
     SET
        parent_id = ' + ISNULL(convert(VARCHAR(MAX), @parent_id), 'NULL') + ', 
        user_id = ' + ISNULL(convert(VARCHAR(MAX), @user_id), 'NULL') + ', 
        item_id = ' + ISNULL(convert(VARCHAR(MAX), @item_id), 'NULL') + ''
EXEC (@TSQL)

edited May 27 '16 at 19:29

Arulkumar

12,966
14
47
68

answered May 27 '16 at 19:16

TLaV

389
2
4

Please explain. I did a test using an integer field (like parent_id is, I'm assuming), using basically the same syntax, with a VARCHAR variable passed to EXEC and it worked. Maybe I'm overlooking something. – TLaV May 27 '16 at 19:23
Your idea worked perfectly, I was just using `COALESCE` to make the string empty, but It I just never though to put the `NULL` inside strings, so using `COALESCE(@var, 'NULL')` made everything work, thanks – Iqbal Khan May 27 '16 at 19:23
@TimLaVenice Sorry my bad, I confused with the `WHERE` clause – Arulkumar May 27 '16 at 19:28
No problem @Arulkumar! – TLaV May 27 '16 at 19:34

T-SQL openquery doesn't update on NULL value

1 Answers1