[AccessD] Recompiling a stored procedure

[Paul Wolstenholme]

David,

Is it possible your temp table still exists after a previous execution?

You could use
    IF OBJECT_ID('tempdb.. #ttmpBWSAccSummary') IS NOT NULL
        DROP TABLE #ttmpBWSAccSummary

Paul Wolstenholme


On Thu, 20 May 2021 at 15:42, 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
>