[AccessD] Recompiling a stored procedure

[Paul Hartland]

Was just going to say your creating a temp table but never dropping it....

You can do this at the start of the stored procedure, cant think if exact
syntax at moment...

If object_id(yourprocedurename,'P') is not null
    Begin
        Drop procedure yourprocedurename
    End

Create yourprocedurename
    Parameters

    With recompile
AS

The rest of your procedure

On Thu, 20 May 2021, 05:00 Paul Wolstenholme, <
Paul.W at industrialcontrol.co.nz> wrote:

> 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
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>