36

A client reported that they were running on SQL Server 2012, and we delivered some test queries for testing prior to a final delivery, however:

'CONCAT' is not a recognized built-in function name.

I understand that CONCAT() is a new built-in function introduced in SQL Server 2012, which is all fine and well, however I have been asked to roll back my change to make this 2008R2 compatible under the guise of "the user executing the query may not have Transact-SQL permissions to execute." So I am just proving my point that the client most likely has a different version of SQL Server installed in DEV than they do in PROD.

I can't find any information about specifically denying SELECT/EXECUTE permissions for built-in scalar valued functions, but is it possible, and if so does the user still receive the same error text?

Paul White
  • 94,921
  • 30
  • 437
  • 687
beeks
  • 1,251
  • 1
  • 8
  • 15

2 Answers2

28

CONCAT was introduced in SQL Server 2012; there is no way to make it work in SQL Server 2008 R2. From the documentation:

enter image description here

There's also no way to make it fail in 2012+, even with compatibility level. So have your people check SELECT @@VERSION; on both servers; you'll find that where CONCAT fails it is < 11. In order to make your code compatible with earlier versions, you'll need to use the standard string concatenation operator (+). I don't know how you would do this with a scalar function, unless you always used the exact same number of input strings and you change your code to use dbo.CONCAT() instead of CONCAT() (there will be scenarios where it matters, plus if your function does anything the native doesn't do, you want consistent behavior if/when you upgrade). So I wouldn't recommend that approach. You may also need to add NULL handling and other minor changes (impossible to tell you how to change your existing script exactly, if we can't see it).

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
6

You can use the ODBC CONCAT function like this:

SELECT {fn CONCAT('foo ', 'test') }

The problem with this is that this function only allows you two parameters at a time. So unless you want to use more than two like this:

SELECT {fn CONCAT('foo ', {fn CONCAT('test ', 'buddy')}) }

You might as well just use the '+' operator.

bfs
  • 79
  • 1
  • 2