T-SQL CONCAT() and +

[NeoPa]

My understanding from these MSDN pages ({CONCAT (Transact-SQL)}, {+ (String Concatenation) (Transact-SQL)} & {SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)}) is that the + will propagate NULLs, where the server's CONCAT_NULL_YIELDS_NULL property is set to ON as mine is, whereas CONCAT() will convert them implicitly to empty strings.

My code, a (relevant) fraction of my View, is :

CONCAT(([Employees].[First Name]+' '),[Employees].[Surname]) AS [EmpFullName]

All records where [Employees].[First Name] is NULL return no text. Those with values in both fields return the full name just as expected. The first CONCAT() parameter should be an empty string if [Employees].[First Name] is NULL or some text with a space on the end if not. Neither should stop the [Employees].[Surname] from getting into the result.

According to my understanding this should be the T-SQL equivalent of Access's :

([Employees].[First Name]+' ') & [Employees].[Surname] AS [EmpFullName]

The latter works but the former doesn't. Am I going mad or am I missing something?

[ck9663]

I was trying to recreate the issue that you're having but I am returning the expected results, with CONCAT_NULL_YIELDS_NULL both set to ON and OFF. Have you tried to remove the "+" sign and just include the space in the concat function?


~ CK

[Rabbit]

Unfortunately I can't test this, our version of SQL Server is too old. And concat is an new function in 2012.

However, you seem to be having a lot of issues with the new functions introduced in 2012. Perhaps the database is set up with an older compatability level?
https://msdn.microsoft.com/en-US/lib...=sql.105).aspx

I can, however, confirm that the CONCAT_NULL_YIELDS_NULL option works as expected in 2008 R2

[NeoPa]

Thank you both for your responses.

@Rabbit.
Great thought. I checked mine though, and it came up with "SQL Server 2012 (110)".

@CK.
I'm not really sure if I correctly understand what you're suggesting I try. Without the + I would surely lose the NULL propagation which is the purpose of the code.

Nevertheless, I tried both of these :

CONCAT(([Employees].[First Name] ' '),[Employees].[Surname]) AS [EmpFullName]

It didn't compile.

CONCAT([Employees].[First Name],' ',[Employees].[Surname]) AS [EmpFullName]

This returns the following error message which is totally in contradiction of the MSDN help page linked above, which explicitly says it handles two or more parameters.

---------------------------
Microsoft SQL Server Management Studio
---------------------------
The CONCAT function requires 2 argument(s).

I did notice, however, and this will hopefully mean something to you guys where it means absolutely nothing to me, that when stored in the SQL View this code has been converted to :

{ fn CONCAT(dbo.Employees.[First Name] + ' ', dbo.Employees.Surname) } AS EmpFullName

I understand it likes to fiddle around with my brackets ([]) for me, but the { fn CONCAT() } seems more than a little weird. Do you get something similar CK? Or does your SQL keep it's fundamental contents?

[Rabbit]

Something is causing your query to revert to an older version of SQL.

The {fn concat()} syntax is an old odbc scalar function that takes only 2 arguments.
https://msdn.microsoft.com/en-us/lib...=sql.105).aspx

How are you accessing SQL Server to create your views? Is it through SSMS? What version of SSMS are you running? Or is it through a different method utilizing ODBC? Which ODBC driver are you using? Is it the most current version?

[NeoPa]

Hi Rabbit.

I'm using MS SQL Server Management Studio and I'm logged in as (Domain) Administrator.

My code is worked out in a text editor (where I can format it clearly) then pasted into the SQL pane of the Design window. As soon as I move to the graphic table display the SQL is updated by SQL Server and that's the result :~)

If I can post more details please let me know. I can see nothing that explains why it would do this.

Questions in order :

1. How are you accessing SQL Server to create your views?
   In SSMS. As described above.
2. Is it through SSMS?
   Yes.
3. What version of SSMS are you running?
   See table below.
4. Or is it through a different method utilizing ODBC?
   No.
5. Which ODBC driver are you using?
   None for this, but I'm using the native driver that came with SQL to connect to it and see the results.
6. Is it the most current version?
   No idea, but it's what has been provided for me. I expect it should be good considering it came with the product which was downloaded fairly recently.

• Microsoft SQL Server Management Studio
  11.0.2100.60
• Microsoft Data Access Components (MDAC)
  6.3.9600.17415
• Microsoft MSXML
  3.0 6.0
• Microsoft Internet explorer
  9.11.9600.17416
• Microsoft .NET Framework
  4.0.30319.34014
• Operating System
  6.3.9600

[Rabbit]

The version numbers look fine. Are you using the query designer or a regular query window? I've heard the query designer can be buggy.

[NeoPa]

I'm not precisely sure what I'm using - or more accurately - what I'm using is called.

From SSMS I display the View, right-click on it and select Design. This opens up the View in what is called a Query Editor Window. That window is split into four distinct panes (which are not named anywhere I can see) :

1. Top - The Table Layout Pane.
   Table layout view including graphic links between table fields.
2. Next down - The Field List Pane.
   A list of all fields used whether to display, filter or sort.
3. Next down - SQL Pane.
   This is where I paste my SQL into.
4. Bottom - The Results Pane.
   This is another - but much slower, way of seeing the results.

[Rabbit]

Yeah that, I heard it was buggy. Try right-clicking the view, script as, alter to new query window. That gives you just the SQL text, edit it there and execute it. That should prevent the query editor from messing with the SQL.

[NeoPa]

Well I'll be {F*£$%} - a son of a gun!

That worked a treat. It looked very similar. It still used the scalar version of the CONCAT() function if I look at what's in there via the Design option, but not when I use the Script to option.

Best of all, it works as advertised.

Thank you so much for sticking with me and finding the solution for me Rabbit.

[Rabbit]

My pleasure. I'll have to admit I don't ever use the designer so it took a while and more than a few Google searches to realize what was happening.

[NeoPa]

So, how do you make updates to objects then? All in T-SQL?

[Rabbit]

For the most part, I use T-SQL to create and update views, stored procedures, function, and tables. I will use the job editor gui to create and schedule jobs on sql server agent. I use SSIS in Visual Studio to create ETL packages and SSAS in Visual Studio to create OLAP cubes.

[NeoPa]

My table manipulation SQL (As opposed to Data processing SQL) has always been pretty lightweight. It's an area I may well get more into. I've done some, just not a great deal and most of that's been quite recent.