2

I am trying to run the same script against SQL 2012 SERVER using SSMS (on Windows OS) and BSQLDB (on RHL Linux). The output for both these clients is different.

The script involves concatenation with null values. here is the simplified version of the script

--File: Test.SQL

declare @FirstValue varchar(50)
declare @SecondValue varchar(50)

set @FirstValue = null
set @SecondValue = 'TEST'

select @FirstValue + ' - Hello - ' + @SecondValue as Result

and the outputs are as below - SSMS

Result
-------------------------
NULL

(1 row(s) affected)

BSQLDB:

 - Hello - TEST

How can I get a consistent result for both these clients.

Kritul Rathod
  • 75
  • 1
  • 11
  • For MSSQL at least, concatenating a string with null results in the whole thing being null. So you could wrap @FirstValue in ISNULL to replace the NULL with a blank string – supergrady Apr 21 '17 at 02:37

2 Answers2

1

The default SQL behavior for concatenation is when any of the value is NULL the whole concatenation results in NULL. however, this was not observed in the query fired from Linux (BSQLDB) .

We had to set the SET CONCAT_NULL_YIELDS_NULL ON connection parameter on SQL to have consistent output from all clients.

Note: Also you can set the same parameter from UI using SSMS,

Right click the server on the Object Explorer > Properties > select Connections > set 'default connection options'

Hope this helps anyone facing similar issue.

Thanks.

Kritul Rathod
  • 75
  • 1
  • 11
  • This pointed me in the right direction :) For me SSMS set a fair few settings by default, which I did not know about until my `bsqldb` wouldn't execute what SSMS would – pietv8x Dec 10 '20 at 13:21
0

in sql-server any value concatenated with NULL is NULL

what you can do in sql-server is use ISNULL function

declare @FirstValue varchar(50)
declare @SecondValue varchar(50)

set @FirstValue = null
set @SecondValue = 'TEST'

select isnull(@FirstValue,'') + ' - Hello - ' + isnull(@SecondValue,'') as Result
RoMEoMusTDiE
  • 4,739
  • 1
  • 17
  • 26
  • Hi maSTAShuFu, That is what I am expecting the whole expression to be evaluated as NULL, but somehow the BSQLDB is treating Null as empty string and returning incorrect value. I am curious to find why the same server instance is responding differently to different clients. – Kritul Rathod Apr 21 '17 at 03:44