I've read that using Dynamic SQL in a stored procedure can hurt performance of your stored procedures. I guess the theory is that the store procedure won't store an execution plan for SQL executed via EXEC or sp_executesql.
I want to know if this is true. If it is true, do I have the same problem with multiple nested IF blocks, each one with a different "version" of my SQL statement?
If you have multiple nested IF blocks then SQL Server will be able to store execution plans. I'm assuming that the IFs are straightforward, eg. IF @Parameter1 IS NOT NULL
SchmitzIT's answer is correct that SQL Server can also store execution paths for Dynamic SQL. However this is only true if the sql is properly built and executed.
By properly built, I mean explicitly declaring the parameters and passing them to sp_executesql. For example
declare @Param1 nvarchar(255) = 'foo'
,@Param2 nvarchar(255) = 'bar'
,@sqlcommand nvarchar(max)
,@paramList nvarchar(max)
set @paramList = '@Param1 nvarchar(255), @Param2 nvarchar(255)'
set @sqlcommand = N'Select Something from Table where Field1 = @Param1 AND Field2 = @Param2'
exec sp_executesql @statement = @sqlcommand
,@params = @paramList
,@Param1 = @Param1
,@Param2 = @Param2
As you can see the sqlcommand text does not hardcode the paramer values to use. They are passed separately in the exec sp_executesql
If you write bad old dynamic sqL
set @sqlcommand = N'Select Something from Table where Field1 = ' + @Param1 + ' AND Field2 = ' + @Param2
exec sp_executesql @sqlcommand
then SQL Server won't be able to store execution plans
@param = value rather than just enumerating the values. Feel free to use the shortcut version if you prefer.This is what MSDN has to say about it. I highlighted the relevant bits to your question
sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch cannot reference variables declared in the batch that calls sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch that calls sp_executesql. Changes in database context last only to the end of the sp_executesql statement.
sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.
