Concatenation of string and NULL

• Elvisn

  SSC Enthusiast

  Points: 161

  More actions

  October 31, 2008 at 12:48 pm

  #72973

  Hi guys.

  I have the follow scenario.

  I'm working with a SQL Server migration from 2000 (running 6.5 compatibility mode) to 2005 and I got a problem with behavior difference between those versions in terms of concatenation results.

  Currently we have a lot of SPs with Select statements concatenating 2 strings where one of them can be NULL. The expected output would be the left not null string.

  i.e

  'string1' + ' string2' = 'string1 string2'

  'string1' + NULL = 'string1'

  But now on SQL Server 2005 it is always returning NULL instead of value from the left string.

  I've researched about this issue and I saw that the option CONCAT_NULL_YIELDS_NULL is a connection-level setting that overrides the db-level one and is always turned ON.

  Do you guys know if I can change something (on server or ODBC/ADO settings) to turn OFF this option?

  We are working with VB6 and ASP.Net using both old and new ODBC/ADO drivers.

  Thanks in advance for your help.

  Elvis

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 31, 2008 at 12:58 pm

  #892535

  Off hand, no. You could modify the procedures to use isnull: isnull(string1,'') + isnull(string2,'').

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  October 31, 2008 at 2:02 pm

  #892572

  I'd think Lynn's solution is the best (meaning - handle these from within the stored procs.

  That being said - it's been a while since I've had to do this - but you should be able to add those to the connection strings, and they shoud be sent to the server:

  concat null yields null = FALSE;

  So your ODBC would look something like

  "DRIVER={SQL SERVER};Server=MyServer;DataBase=MyDB;Trusted Connection=True; concat null yields null = FALSE;"

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  October 31, 2008 at 2:08 pm

  #892575

  Matt Miller (10/31/2008)

  ---

  I'd think Lynn's solution is the best (meaning - handle these from within the stored procs.

  That being said - it's been a while since I've had to do this - but you should be able to add those to the connection strings, and they shoud be sent to the server:

  concat null yields null = FALSE;

  So your ODBC would look something like

  "DRIVER={SQL SERVER};Server=MyServer;DataBase=MyDB;Trusted Connection=True; concat null yields null = FALSE;"

  Of course - it also might be with the underscores too (like I said - been a while)

  "DRIVER={SQL SERVER};Server=MyServer;DataBase=MyDB;Trusted_Connection=True; concat_null_yields_null = FALSE;"

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?