Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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