[AccessD] Recompiling a stored procedure

[David Emerson]

Hi Listers,

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

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
happens:

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
(0),
	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
(50),
	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)

DECLARE cc CURSOR
FOR SELECT DISTINCT Purpose, DispPreference 
FROM  @ttmpBWSCapitalAccount
ORDER BY DispPreference

OPEN cc
FETCH NEXT FROM cc INTO @Purpose, @DispPreference
WHILE @@fetch_status=0
	BEGIN
		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
	END
--Reverts to old value here:					Yr	Age
Cash	Roth	Pension
SELECT * FROM #ttmpBWSAccSummary
CLOSE cc
DEALLOCATE cc

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

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?

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand