Re: Temp Tables, Transaction and Loops
From: Jan Schoenrock (JanSchoenrock_at_discussions.microsoft.com)
Date: 09/21/04
- Next message: Uri Dimant: "Re: Temp Tables, Transaction and Loops"
- Previous message: Marcin Gorzynski: "Re: Date Range design question"
- In reply to: Uri Dimant: "Re: Temp Tables, Transaction and Loops"
- Next in thread: Uri Dimant: "Re: Temp Tables, Transaction and Loops"
- Reply: Uri Dimant: "Re: Temp Tables, Transaction and Loops"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 21 Sep 2004 02:25:02 -0700
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
>
>
>
- Next message: Uri Dimant: "Re: Temp Tables, Transaction and Loops"
- Previous message: Marcin Gorzynski: "Re: Date Range design question"
- In reply to: Uri Dimant: "Re: Temp Tables, Transaction and Loops"
- Next in thread: Uri Dimant: "Re: Temp Tables, Transaction and Loops"
- Reply: Uri Dimant: "Re: Temp Tables, Transaction and Loops"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
