Today I tripped over what turned out to be a difference in the way sp_executesql behaves between SQL Server 2005 and 2008 when executing a string containing a parameterised stored procedure call.
Take this simplified example:
DECLARE @SQL NVARCHAR(256)
SET @SQL = 'sp_help @obj'
EXECUTE sp_executesql @SQL, N'@obj NVARCHAR(100)', 'sp_help'
In SQL Server 2008 (10.0.4000.0), the above executes successfully.
In SQL Server 2005 (9.00.1399.06), it throws the following exception:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_help'.
Adding the “EXEC(UTE)” before the stored procedure name in @SQL resolves the issue in 2005. As standard, I (usually) standardise on ensuring all stored procedure calls are made with “EXEC(UTE)” even when it is the only statement in the batch. Obviously, in this case it was overlooked and tripped me up!