I have a problem that works in SQL Server 2017 but not in SQL Server 2019. It is related to tempdb caching. This has to do with creating temporary tables in stored procedures and changing its structure using dynamic SQL. We have a need to do that for various dynamic reporting needs. The first time it is called, the structure is cached and subsequent call to the procedure fails or returns invalid results. How do I prevent caching of such tables? Below is some sample code and how come it works in 2017. Help appreciated.
CREATE PROCEDURE [dbo].[tempDBCachingCheck]
@yearList varchar(max)
AS
BEGIN
SET NOCOUNT ON
DECLARE @yearCount int
DECLARE @yearCounter INT
DECLARE @yearValue INT
DECLARE @sql nvarchar(max)
-- With table variable
DECLARE @tempYearList TABLE (id INT IDENTITY(1,1), rpt_yr int)
INSERT INTO @tempYearList (rpt_yr)
SELECT value FROM STRING_SPLIT(@yearList, ',');
SELECT * FROM @tempYearList
--------------------------------------------------------------------
--With temporary table, since we will be altering this with dynamic sql
CREATE TABLE #returnTable (id INT IDENTITY(1,1))
-- Tried adding a named constraint to not make it cache, but does not work
ALTER TABLE #returnTable
ADD CONSTRAINT UC_ID UNIQUE (id);
SELECT @yearCount = COUNT(*) FROM @tempYearList
-- Add the years as columns to the return table to demostrate the problem
SET @sql = N'ALTER TABLE #returnTable ADD '
SET @yearCounter = 1
WHILE @yearCounter <= @yearCount
BEGIN
SELECT @yearValue = rpt_yr FROM @tempYearList WHERE id = @yearCounter
IF @yearCounter > 1
SET @Sql = @Sql + N', '
SET @sql = @sql + N' [' + convert(varchar(20), @yearValue) + N'] float'
SET @yearCounter = @yearCounter + 1
END
EXECUTE sp_executesql @sql
SELECT * FROM #returnTable
-- No need to drop the temporary tables but doing just in case
DROP TABLE #returnTable
END
GO
-- run these statements and you will see the second call with return the cached #returnTable
EXEC tempDBCachingCheck '2019,2020'
EXEC tempDBCachingCheck '2017,2018,2019,2020'
GO
-- Clear temp table cache and call in reverse order, then will hit an error
-- 'A severe error occurred on the current command. The results, if any, should be discarded.'
USE tempDB
GO
DBCC FREEPROCCACHE
GO
EXEC tempDBCachingCheck '2017,2018,2019,2020'
EXEC tempDBCachingCheck '2019,2020'
GO