Openquery with TSQL: how to insert NULL in nvarchar column?

Question

Im trying to use a stored procedure to insert some string values into a remote DB with Openquery. The argument @val_a can sometimes be some random string, but sometimes it can be NULL.

Following syntax works if @val_a is NULL, but not if it's a string 'asdf'.

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

But, in order to work with the string 'asdf', the syntax has look like this:

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

But here, NULLs are inserted as the string 'NULL', not as a NULL value.

Is there a way to write the TSQL query in such form that both NULL and 'asdf' are inserted correctly in the table?

Answer 1

You may use like that:

val_a = ' + ISNULL(convert(VARCHAR(MAX), '''' + @val_a + ''''), 'NULL') + ';'