0

I would like to ask how can I concatenate a nvarchar column with NULL without getting NULL? I would like to create an INSERT script using SELECT, but when any of the values is NULL, I get only NULL.

SELECT TOP 10 
    'IF NOT EXISTS(SELECT 1 FROM tblParameterKey 
                   WHERE keyNames='''
        + CAST(ISNULL([keyNames], 'NULL') AS NVARCHAR(255)) + ''')
     BEGIN
         INSERT INTO tblParameterKey VALUES(''' + CAST(ISNULL([keyNames], 'NULL') AS NVARCHAR(255))+''')
     END'
FROM tblParameterKey

This query returns correct insert inly when value is not null. If value is null it returns insert with 'NULL' which is not correct because it will be inserted as varchar and not as null value. If I remove ' ' the whole result will become null.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nogi
  • 134
  • 9

1 Answers1

1

Edit - Maybe put the NULLIF statement inside the query string that way it will be null instead of string null. 

SELECT TOP 10 
    'IF NOT EXISTS(SELECT 1 FROM tblParameterKey 
                   WHERE keyNames=NULLIF('''
        + [keyNames] + ''', 'NULL') AS NVARCHAR(255))
     BEGIN
         INSERT INTO tblParameterKey VALUES(NULLIF(''' + [keyNames]+''', 'NULL') AS NVARCHAR(255))
     END'
FROM tblParameterKey
Joshua Duxbury
  • 4,892
  • 4
  • 32
  • 51
  • that won't work, this is just and example query, there is many columns and some of then are nullable and i need insert scripts for whole table, so i need null values. – Nogi Jul 31 '17 at 10:19
  • That will do the same thing as giving NULL instead of 'NULL' into ISNULL function, whole result will be null. – Nogi Jul 31 '17 at 10:31
  • Edited my answer @Nogi – Joshua Duxbury Jul 31 '17 at 10:42
  • For now it looks like it will workt, you just need to go with +CAST(ISNULL([keyNames],'NULL') AS NVARCHAR(255))+ instead of +keyNames+ because it would again result into NULL. this way it will generate INSERT INTO tblParameterKey VALUES(NULLIF('NULL', 'NULL')) Thank you! – Nogi Jul 31 '17 at 10:58