For me, sp_executesql is usually used inside a stored procedure for dynamic SQL statements, by which the exact list of columns, tables, or WHERE / GROUP BY / HAVING clauses can only be known during run time.
From your post, I expect that you are trying to compare the performance differences between the different statements that are to be placed inside your stored procedures (separately), and thus, your enquiry relates to caching (and recompilation) of query plan in SQL Server.
Before executing a stored procedure, SQL Server tries to obtain a query plan of that stored procedure from the plan cache (there are exceptions). If a suitable query plan cannot be found from the plan cache, SQL Server compiles a query plan for your stored procedure. This is the compilation cost before executing the query plan.
*** *** *** *** *** ***
*** dbo.usp_ListWorkOrder01 *** For your first statement starting with 'DECLARE...'
*** *** *** *** *** ***
Please note that I have modified your statement such that it takes a parameter, instead of assigning TBL1.PRODUCE to a constant (@Produce).
CREATE procedure [dbo].[usp_ListWorkOrder01] @sProduce int = 0
as
DECLARE @MySQL nvarchar(500)
DECLARE @MyParmDefinition nvarchar(500)
set @MySQL = 'SELECT * FROM TBL1 INNER JOIN TBL2 ON TBL1.ID=TBL2.ID
+ ' WHERE TBL1.PRODUCE= @MyProduce'
EXECUTE sp_executesql @MySQL, @MyParmDefinition, @MyProduce = @sProduce
GO
SQL Server creates execution plans for the following.
(1) Stored procedure [dbo].[usp_ListWorkOrder01]
(2) Prepared statement 'SELECT * FROM TBL1 INNER JOIN TBL2 ON TBL1.ID=TBL2.ID WHERE TBL1.PRODUCE= @MyProduce
*** *** *** *** *** ***
*** dbo.usp_ListWorkOrder02 *** For your second statement...
*** *** *** *** *** ***
CREATE procedure [dbo].[usp_ListWorkOrder02] @sProduce int = 0
as
SELECT * FROM TBL1 INNER JOIN TBL2 ON TBL1.ID=TBL2.ID
WHERE TBL1.PRODUCE = @sProduce
GO
SQL Server creates execution plan for the following.
(1) Stored procedure [dbo.][usp_ListWorkOrder02]
*** *** *** *** *** ***
In any case, SQL Server requiers query plans for execution.
In dbo.usp_ListWorkOrder02, the whole query plan is retrieved (or compiled) once, and then executed.
In dbo.usp_ListWorkOrder01, additional execution overhead is added by calling the stored procedure sp_executesql, which includes getting (or compiling) the query plan of the prepared statement (SELECT), and the related execution context for executing the query plan.
=== For more information ===
~ Parameters and execution plan reuse
http://msdn.microsoft.com/en-us/library/ms175580.aspx
~ sys.syscacheobjects (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms187815.aspx
