[AccessD] Recompiling a stored procedure

David Emerson newsgrps at dalyn.co.nz
Wed May 19 22:42:06 CDT 2021

Hi Listers,

I have a strange situation which I think is linked to optimisation by SQL

I have a stored procedure which creates a temporary table.  The columns are
created based on the values of another table.  The stored procedure works
fine unless the there is little difference between the table when the code
was previously run and the current run.

For example, if the first run produces a table with these columns:	Yr
Age	Cash
And the send run produces a table with these columns:			Yr
Age	Cash	Roth	Pension
Then the second table has the correct column headings.

However, if the first run produces a table with these columns: 		Yr
Age	Cash	Roth	Pension
And the send run should produce a table with these columns:		Yr
Age	Cash	Roth	Retirement
Then the final table has the same column headings as the first.

The code is picking up the correct data for the headings and is initially
creating the correct headings but when the table has finished being created
the headings are the same as the previous run.

I have put debugging statements in the code and identified where the change

DECLARE @ttmpBWSCapitalAccount table(CapAccID int, Type smallint NULL
DEFAULT (0), Owner varchar (100) NULL, FBOwner smallint NULL DEFAULT (0),
	Purpose varchar (100) NULL, Year smallint DEFAULT (0), AccStart
money DEFAULT (0), Age smallint NULL DEFAULT (0), AgeSp smallint DEFAULT
	GoalAmt money DEFAULT (0), Savings money DEFAULT (0), AnnualReturn
real NULL DEFAULT (0), Growth money DEFAULT (0), AccEnd money DEFAULT (0),
	CostBasisBeforeGoals money DEFAULT (0), CostBasis money NULL DEFAULT
(0), AssocComp smallint DEFAULT (3), DispPreference smallint NULL DEFAULT
	ReqMinDist money DEFAULT (0), ClientAgeAvail smallint DEFAULT (0),
TaxRateLTCG real NULL DEFAULT (0) )

INSERT INTO @ttmpBWSCapitalAccount
SELECT * FROM dbo.fnGetttmpBWSCapitalAccount(@BWSClientID, @txtAnalysisYrs,
@ClientAgeCalc, @SpouseAgeCalc)

/* Generate a new crosstab table */ 
CREATE TABLE #ttmpBWSAccSummary (Yr int, ClSpAge Varchar(10))

DECLARE @Yr int, @Purpose varchar (100), @DispPreference smallint, @col
nvarchar(100), @strsql nvarchar(4000)

FOR SELECT DISTINCT Purpose, DispPreference 
FROM  @ttmpBWSCapitalAccount
ORDER BY DispPreference

FETCH NEXT FROM cc INTO @Purpose, @DispPreference
WHILE @@fetch_status=0
		SELECT @col = @Purpose 
		/*Alter Table #ttmpBWSAccSummary add @col  money */
		SELECT @strsql = 'Alter table #ttmpBWSAccSummary add [' +
@col + '] money'
		EXEC (@strsql)
		FETCH NEXT FROM cc INTO @Purpose, @DispPreference
--Next line shows correct column headings for the second run:	Yr	Age
Cash	Roth	Retirement
SELECT @Purpose, * FROM #ttmpBWSAccSummary
--Reverts to old value here:					Yr	Age
Cash	Roth	Pension
SELECT * FROM #ttmpBWSAccSummary

I have made comments in the last 7 lines above which shows where the problem

I have found that if I run the sproc with the WITH RECOMPILE command that
the problem is solved:

Eg exec dbo.rptSpBWSoAccSummary 21640, 67, 33, 0 WITH RECOMPILE

However, this project is being run in Visual Studio it will take me some
effort to arrange the changing of that code (it is not something I have much
control over).

Is there some way to force a recompile within a stored procedure?


David Emerson
Dalyn Software Ltd
Wellington, New Zealand


More information about the AccessD mailing list