N'SELECT * FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level',
@level = 109,@MessageIn Output;
1. EXECUTE() :: If we write a query which takes a parameter lets say "EmpID". When we run the query with "EmpID" as 1 and 2 it would be creating two different cache entry (one each for value 1 and 2 respectively).
It means for Unparameterised queries the cached plan is reused only if we ask for the same id again. So the cached plan is not of any major use.
2. SP_EXECUTESQL() :: In the similar situation for "Parameterised" queries the cached plan would be created only once and would be reused 'n' number of times. Similar to that of a stored procedure. So this would have better performance.
A Simple usage of Replace Function
REPLACE ( string_expression1 , string_expression2 , string_expression3 )
SELECT Replace('OurTeam.com Rocks!', 'Rocks', 'Rolls')