I am getting runtime error while calling a stored procedure (as shown below) in SQL Server 2012 but not in SQL Server 2014, it looks very fundamental, but I would like to know exact reason and if there is any work around.
It runs just fine in SQL Server 2012 against a database with compatibility 90:
SP_AddState 29,merge
But it causes an error in SQL Server 2014:
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'merge'.
It works only when called as
SP_AddState 29,'merge'
What is the reason of this execution behavior?
The MERGE operator was added to SQL Server in 2008 and, at the same point, MERGE was made a Reserved Keyword. As a result when using an unquoted literal with the value MERGE it will be seen as the start of the MERGE operator and that is not allowed when using EXEC.
Although it was added in 2008 (and you are using 2012), if you are using an old compatibility level (90 or lower) then the keyword may not be treated as one, as it wasn't a reserved keyword in SQL Server 2005. This would explain why you are getting different behaviour in SQL Server 2012 and 2014, as 2012 did support compatibility 90, where as 2014 did not: Support matrix. If your database was at level 90 and restored on a 2014 instance, its compatibility level would have been raised.
Of course, in reality, you really should not be using compatibility 90 anymore (again, that's SQL Server 2005!), or even SQL Server 2012 (that reached end of life 2 years ago), and even 2014 reaches end of life in less than a month. Also, although the syntax EXEC <ProcName> (@ParameterName = ) <Literal> is allowed, you really shouldn't be using it for strings; use a string literal. 'merge' is the syntax you should be using. This avoids the problem above, as well as if you were to have other reserved keywords, or if you have a string that needs to be delimited (starts with a digit, has non-alphanumerical characters, etc).