Will the below stored procedure be recompiled on every run, because of the dynamic SQL within it? Are there any serious performance hits with this method?
CREATE PROCEDURE MyStoredProcedure
AS
BEGIN
DECLARE @column varchar(3)
SELECT @column = 'd' + CAST(DAY(GETDATE()) AS char)
EXEC ('SELECT ' + @column + ' FROM t1')
END
It is better to avoid dynamic SQL if possible, is true. For a more detailed discussion, see this SO thread. However, to address specifically your question: the EXEC ('SELECT d21 FROM t1') statement would behave exactly as if a request from the client containing the script SELECT d21 FROM t1 was received by the server. If the plan is already in the cache, it will be executed from the cache (sparing some details like metadata version changes etc etc). If not, it will be compiled, a new plan created, and run. Note that a query like SELECT field FROM Table is what is called a 'trivial plan query', the cost of generating a plan for something like this is completely negligible on most systems.
No, the procedure should not be recompiled every run and even if it would, the performance of the call would not be lot worse as the dynamic SQL already slows down enough. This part of the procedure is parsed and executed every run.
