sp_prepexec (sp_execute) vs. sp_executeSQL

The meat of the question: are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as sp_executeSQL / sp_execute also take advantage of them? I am not a DBA, so please use little words. Our application sends over prepared statements that, from the profiler, I see run all SQL through sp_prepexec which is a system procedure for both running sp_prepare and sp_execute. What I'm trying to do is figure out if I am benefiting from temp table caching. I've been using this guide with object_id() to examine behavior https://sqlkiwi.blogspot.com/2012/08/temporary-tables-in-stored-procedures.html Then point #3 on this blog post suggests that EXEC cannot use temp table caching, but leaves out whether sp_executeSQL can: [Link](https://learn.microsoft.com/en-us/archive/blogs/turgays/exec-vs-sp_executesql) In my query sent over via the client I have created a simple temp table. DECLARE @foo int; -- set by JDBC, unused but required to force a prepared statement SELECT 1 AS id INTO #tmp SELECT OBJECT_ID('tempdb..#tmp'); In profiler, I can see: declare @p1 int set @p1=NULL exec sp_prepexec @p1 output,N'@P1 int',N'declare @foo INT = @P1 SELECT 1 as id into #tmp select Object_id(''tempdb..#tmp''); DROP TABLE #tmp;',1 select @p1 I also get a cachehit from this. However, the object_id of the temp table appears to be changing on me, which is not the behavior I would see if this temp table were created in a real stored procedure. However, when I run this same code through sp_executeSQL, I'm also seeing that the object_id of the temp table has changed. This leads me to believe that only "real" user created stored procedures take advantage of temp table caching.