Re: Temp Tables, Transaction and Loops

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Uri Dimant (urid_at_iscar.co.il)
Date: 09/21/04 

Date: Tue, 21 Sep 2004 12:41:05 +0200

Jan
It's because I used SELECT * INTO technique to create the table an in the
same batch i tried to add the column and update it.
At this time SQL Server does not see the column was added and using
sp_executesql causes it to perfom it as another batch

"Jan Schoenrock" <JanSchoenrock@discussions.microsoft.com> wrote in message
news:37A14C94-78BA-4E55-A7CA-3E336F8BC963@microsoft.com...
> Hello Uri,
>
> thanks for your answer. I will try it. But I do not understand why I must
> use EXEC sp_executesql for simple operations ( in your example updates),
if
> it runs.
> Is it performant and what is the reason for using EXEC sp_executesql in
this
> case. If I've a string containing SQL it is clear, but in this case with
> static SQL ?
> Can you tell me about the technical background ?
>
> Thanks Jan
>
>
>
> "Uri Dimant" wrote:
>
> > Jan
> > I simplified the problem but it should be shed a light for you
> > CREATE PROC spmyproc
> > AS
> > SELECT OrderId INTO #Temp FROM NorthWind.dbo.Orders
> > ALTER TABLE #Temp ADD col INT
> > UPDATE #Temp SET col=1
> >
> > GO
> >
> > EXEC spmyproc
> > ----Server: Msg 207, Level 16, State 1, Procedure spmyproc, Line 5
> > -----Invalid column name 'col'.
> >
> > ALTER PROC spmyproc
> > AS
> > SELECT OrderId INTO #Temp FROM NorthWind.dbo.Orders
> > ALTER TABLE #Temp ADD col INT
> > EXEC sp_executesql N'UPDATE #Temp SET col=1'
> >
> > EXEC spmyproc
> > ----Now it works
> > OR
> > ALTER PROC spmyproc
> > AS
> > SELECT OrderId,CAST(NULL AS INT)as col INTO #Temp FROM
NorthWind.dbo.Orders
> > UPDATE #Temp SET col=1
> >
> > EXEC spmyproc
> > ----Now it works
> >
> > drop proc spmyproc
> >
> >
> >
> > "Jan Schoenrock" <JanSchoenrock@discussions.microsoft.com> wrote in
message
> > news:C8CCEFF6-FDAF-478A-84BF-AA46ABC758A1@microsoft.com...
> > > Hi,
> > >
> > > I'm running SLQ Server 2000 and I've had the following Problem:
> > >
> > > When I'm using a temp table within a stored procedure and call this
stored
> > > procedure from another stored proc within a loop and a transaction I
> > always
> > > get a mass of error messages. The content of these messages is always
the
> > > same: "Ivalid column 'x'" (translated) but x is a valid col of the
temp
> > > table and the temp table is created.
> > >
> > > If I call the stored proc from another stored proc without loop and
> > > transaction it runs well.
> > >
> > > Some details to the environment of this problem:
> > > I have to find solutions for some optimizing tasks. All these task are
in
> > a
> > > Table T1 and depending on the task it can be a minimizing or a
maximizing
> > > case.
> > > To optimize a stored proc SP1 loops through the rows in T1 and each
row
> > > treatmend is encapsulated in a transaction.
> > > Depending on the case of the row (min or max) within the loop the
> > "minimize
> > > stored proc" (SP2) or the "maximize stored proc" (SP3) is called. SP2
or
> > SP3
> > > are very similar but for performance and readability maximizing and
> > > minimizing are devided into two stored proc. The beginning of SP2,SP3
is
> > to
> > > create a temp table #Solution, and at the end of SP2,SP3 #Solution is
> > dropped
> > > by "DROP TABLE".
> > > SP2, SP3 are inserting their results into a regular table and are
> > returning
> > > only some parameters back to SP1.
> > > SP1 commits the transaction and loops to the next row, begins a new
> > > transaction, calls SP2 or SP3 and so on.
> > >
> > > Because I did not find a way to solve this problem by holding on using
the
> > > temp table version, now I'm using a regular table without further
changes
> > and
> > > it runs very well.
> > >
> > > Greetings Jan
> > >
> > >
> > >
> > >
> > > --
> > > Jan Schoenrock, Hamburg, Germany
> >
> >
> >