[AccessD] Recompiling a stored procedure

[Ryan Wehler]

I thought I’d read that temporary tables as a variable using @ instead of #tblName the estimator is USUALLY expecting a one row return, which might be throwing things off?

The other listers suggested dropping the table as well, but should be happening when the proc falls out of scope. 



> On May 19, 2021, at 10:42 PM, David Emerson <newsgrps at dalyn.co.nz> wrote:
> 
> 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
> 
> 
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com