Dynamic Statements - sp_executesql Vs EXEC

Dynamic Statements are ineffective to use as SQL Server would not be able to determine a execution during compilation time and so SQL Server would create the execution plan
each and every time when such statements are executed.

The greatest advantage of Stored Procedure apart from reusability is that the code is compiled and so SQL would save some execution time by not invoking compiler for creating
creating execution plan. What would happen if we need to use dynamic statements in our procedure due to a business need would the same be compiled once and then cached or does it each time create execution plan ?

Answer would be it depends on how we invoke dynamic statements. Generally there are 2 ways of invoking dynamic statements one to EXEC statement and the next one would be
to use sp_executesql . Of which sp_executesql is the one which needs to be used as the plan is cached for the same.

As always let us see an example to understand this better:

IF EXISTS ( SELECT 'X' FROM sysobjects where TYPE = 'U' and name = 'TEST_SYS_CACHE')
DROP TABLE TEST_SYS_CACHE
GO


CREATE TABLE TEST_SYS_CACHE
( Col1_Int INT IDENTITY(1,1) NOT NULL,
Col2_Varchar VARCHAR(255) NOT NULL,
Col3_Varchar VARCHAR(2000) NOT NULL,
COl4_Flag BIT NOT NULL
)

--Poplulate 1000 rows
DECLARE @iCount INTEGER
SELECT @iCount = 1

WHILE @iCount < = 1000
BEGIN

INSERT INTO TEST_SYS_CACHE(Col2_Varchar,Col3_Varchar,COl4_Flag)
SELECT 'ABC' + CONVERT(VARCHAR,@iCount),'\\ABC' + CONVERT(VARCHAR,@iCount) + '\Share',1

SELECT @iCount = @iCount + 1

END

DBCC FREEPROCCACHE

--Execute this query multiple times and you would see usecount is always 1
DECLARE @Col_Value_E INT
DECLARE @Qry_Cache_E VARCHAR(7000)
SELECT @Col_Value_E = 100
SELECT @Qry_Cache_E = 'SELECT * FROM TEST_SYS_CACHE WHERE Col1_Int = ' + CONVERT(VARCHAR,@Col_Value_E)
EXEC (@Qry_Cache_E)
SELECT sql,usecounts,* from sys.syscacheobjects WHERE objtype = 'Prepared'

DBCC FREEPROCCACHE

--Execute this query multiple times and you would see usecount keeps increasing
DECLARE @Col_Value INT
DECLARE @Qry_Cache NVARCHAR(4000)
SELECT @Col_Value = 100
SELECT @Qry_Cache = 'SELECT * FROM TEST_SYS_CACHE WHERE Col1_Int = @Col_Value'
EXEC sp_executesql @Qry_Cache, N'@Col_Value INT', @Col_Value = @Col_Value
SELECT sql,usecounts,* from sys.syscacheobjects WHERE objtype = 'Prepared'


/* Output
sql usecounts
(@1 tinyint)SELECT * FROM [TEST_SYS_CACHE] WHERE [Col1_Int]=@1 1


sql usecounts
(@Col_Value INT)SELECT * FROM TEST_SYS_CACHE WHERE Col1_Int = @Col_Value 4

/* Output

So from the above we can infer that sp_executesql plan is cached and used for subsequent runs but for EXEC each time the plan is recreated