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?

3
  • If concat is not working then try this one SELECT (c.FirstName+','+c.LastName) AS FULLNAME FROM graduatejob c; May 2, 2018 at 7:02
  • CONCAT supports decimal type concat(ID + '_' + OtherID), ID can be int type.
    – Zhang
    Aug 28, 2018 at 7:15
  • In older versions, use this instead: DECLARE @msg VARCHAR(200) = 'Shrink file completed. Target Size: ' + @TargetSize + ' MB. Timestamp: ' + CURRENT_TIMESTAMP; Mar 27, 2019 at 16:40

2 Answers 2

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).

4
  • Thanks, Aaron. The code is trivial to change, as CONCAT() is just used to join some strings together before a SP_ExecuteSQL dynamic SQL call. I really just wanted to confirm that there was NO way to deny access to using CONCAT() in SQL Server 2012
    – beeks
    Feb 1, 2016 at 15:16
  • 1
    @beeks I don't know of any way to deny users the ability to use CONCAT(), no. I don't quite follow what bearing that has on what you need to do to make the code work on 2008 R2, though. You need to remove CONCAT(), not add it. Feb 1, 2016 at 15:17
  • 1
    yes, I understand how to make the code compatible with 2008 R2, that is trivial. Thanks for confirming that one cannot DENY access to this function.
    – beeks
    Feb 1, 2016 at 15:25
  • Just to note when changing the concat to the + you'll need to ensure values being concatenated are strings to begin with, if you have any numerical fields it will try to add them (and fail because a string is not an int) so if there is a numeric field (type) make sure you convert / cast it to a string first
    – Ste Bov
    May 2, 2018 at 7:33
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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.