Forum Discussion

MattSontum's avatar
MattSontum
Occasional Reader
Sep 19, 2025

Bug: "Invalid column name" after sp_recompile for valid column in custom data type

Hello, I was hoping to find a workaround for an issue I am having. I believe this is a bug in SQL Server (we are using SQL Server 2022)

I have written a utility stored procedure for solving the gaps and islands problem, utility.GetTimeIslands, which returns the longest contiguous islands from the time islands passed into it. This procedure utilizes a custom datatype, utility.TimeIslandList, which is defined as

CREATE TYPE utility.TimeIslandList AS TABLE (
	ISLAND_ID			BIGINT		NOT NULL,
	KEY_1				BIGINT		NOT NULL,
	KEY_2				BIGINT		NOT NULL,
	KEY_3				BIGINT		NOT NULL,
	START_DATE			DATE		NOT NULL,
	END_DATE			DATE		NOT NULL,
	PRIMARY KEY (ISLAND_ID)
);

The call to utility.GetTimeIslands in PRC.LoadDeals is

DECLARE Til utility.TimeIslandList;
INSERT INTO Til SELECT STG_DEAL_SCHEDULE_ID, DEAL_ID, PRICE_ACCOUNT_SET_ID, PRICE_ITEM_ID, START_DATE, END_DATE FROM #DealSchedulePrep;
INSERT #LatestTimeIslands EXEC utility.GetTimeIslands Til;

If PRC.LoadDeals is called after a server reboot, or after

EXEC sp_recompile 'utility.GetTimeIslands';

Is called, it throws the error

"Msg 207, Level 16, State 1, Procedure utility.GetTimeIslands, Line 60 [Batch Start Line 8]
Invalid column name 'KEY_3'."

Line 60 corresponds to the first statement in utility.GetTimeIslands where the custom data type is referenced

WITH ChangePoints AS (
	SELECT KEY_1, KEY_2, KEY_3, START_DATE as X_DATE FROM Til
	UNION SELECT KEY_1, KEY_2, KEY_3, DATEADD(DD, CASE END_DATE WHEN '9999-12-31' THEN 0 ELSE 1 END, END_DATE) FROM Til
)
...

However, if

DECLARE Til utility.TimeIslandList;
EXEC utility.GetTimeIslands Til;

Is called prior to calling PRC.LoadDeals, then PRC.LoadDeals does not throw the error. And PRC.LoadDeals will execute successfully until utility.GetTimeIslands is recompiled.

Another interesting twist is that adding those lines to PRC.LoadDeals, prior to the actual call, does not help. It throws the same error on the empty call, if the empty call is made inside the stored procedure before the empty call is made outside the stored procedure. For that matter, once any call is made to utility.GetTimeIslands outside the stored procedure, calls made to it inside the stored procedure will work.

Has anyone ever seen anything like this? If so, is there a solution outside of making the GetTimeIslands empty call after each server restart? Does anyone have a guess as to why the error message calls out KEY_3 specifically?

1 Reply

  • martin_smith's avatar
    martin_smith
    Copper Contributor

    Has anyone ever seen anything like this?

    Yes. I've seen this sort of thing when there are #temp tables named the same in both parent and child scopes.

    See this example DB Fiddle https://dbfiddle.uk/H05-MO65 

    Line numbers often just point to the statement that has the problem rather than the actual specific line so per the additional code you showed in the https://stackoverflow.com/q/79769659/73226 this statement is, in fact, a couple of CTEs followed by 

    INSERT INTO #Islands (KEY_1, KEY_2, KEY_3, START_DATE, END_DATE)

    So you could definitely see this type of thing if the outer proc also has a temp table called #Islands but without the KEY_3 column

     

     

     

Resources