0

In a dynamic SQL server query i want to build a dynamic insert script based on other table.

SELECT 'INSERT INTO MyTable(column) VALUES ' + Concat ('(',ISNULL(''''+ ColumnValue+'''', 'NULL'), ')' ) from myOriginalTable

and I want it to return 'NULL' as a varchar value if the column value is null in the original table. But when column value is null: (basically in SSMS) : select ISNULL(''''+ NULL+'''', 'NULL') then the returned value is 'NUL' varchar instead of NULL, and i don't know why. When i try the query outisde dynamic context it works: 

select isnull(NULL,'NULL')

Is this a feature or a bug?

EDIT

This is not table definition related, to reproduce the issue just run select ISNULL(''''+ NULL+'''', 'NULL') in SSMS or find the result from an online platform

LeviTheOne
  • 105
  • 9
  • `Concat ('(',ISNULL(''''+ ColumnValue+'''', 'NULL'), ')' )` is a really bad idea. It's wide open to injection. – Thom A Nov 02 '18 at 11:00
  • @Larnu it's a script only for myself, i want to create a one time insert script based on existing table data – LeviTheOne Nov 02 '18 at 11:02
  • 1
    Why does it need to be dynamic anyway? What's wrong with `INSERT INTO MyTable (Column) SELECT ColumnValue FROM myOriginalTable;`? – Thom A Nov 02 '18 at 11:04
  • 2
    I bet your column is a NVARCHAR or VARCHAR (3). – mxix Nov 02 '18 at 11:05
  • Can we see the table schema? (right click, script table as->create to->scripting window) – Phill Nov 02 '18 at 11:18

2 Answers2

2

One of the many reasons why you should generally prefer COALESCE over ISNULL1.

select COALESCE(''''+ NULL+'''', 'NULL')

returns NULL when the same with ISNULL, as you stated, returns NUL.

Why? Because ISNULL doesn't use the normal rules for data type precedence that are used elsewhere in SQL Server and instead just forces the result to have the same data type as its first argument, which for the above is apparently some kind of [n][var]char(3) type.

On the other hand, COALESCE considers the types of all of its arguments (another reason to prefer it - generalizing to more than two arguments) to determine the appropriate data type for its result. Since 'NULL' is a char(4) literal, the resulting data type must be able to accommodate at least 4 characters.

1The only place I used to use ISNULL instead is in the definition of computed columns/expressions in views, where the database engine used to analyze ISNULL and COALESCE differently, and I wanted it to compute that the resulting data type was not null. Not sure if this difference still exists.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
1

because either

  1. You have a row in your table in which ColumnValue contains the literal string 'NUL'

  2. The first row(s) generated by your dynamic query are no more than 3 characters wide, so the engine deduces that it should output a varchar(3) column. Then 'NULL' gets truncated to 'NUL'

In particular, if the first row of your query is one where ColumnValue is null then the type of the expression ISNULL(''''+ NULL+'''', 'NULL') is evaluated as the type of ''''+ NULL+''''

The 2nd case you should be able to resolve by something like

Concat ('(', ISNULL(Cast(''''+ Null +'''' as VarChar(4)), 'NULL'), ')' )
Chris F Carroll
  • 11,146
  • 3
  • 53
  • 61
  • a more likely explanation, would be that the column named **column** in the table **MyTable** is (n)varchar(3) – t-clausen.dk Nov 02 '18 at 12:33
  • 1
    It's not table related, nor column length: Just open up SSMS and enter the following `select ISNULL(''''+ NULL+'''', 'NULL')`. From an online platform the [result](https://rextester.com/BHOQ22547) is the same. – LeviTheOne Nov 02 '18 at 12:43
  • It is a case of clause 2, but driven by the datatype of `''''+ NULL+''''` . But my fix was wrong -- the Cast has to be inside the IsNull – Chris F Carroll Nov 02 '18 at 13:23