Temp Tabellen in Stored Procedure

[Jens Schumann]

Hallo,
ich habe das Problem, dass ich innerhalb einer Stored Procedure nicht auf eine
temporäre Tabelle zugreifen kann. Innerhalb eines SQL_Statements kann
ich nicht auf @Aliastable.ALIAS zugreifen. Fehlermeldung ist Codebeispiel.

SQL-Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER   Procedure [dbo].[ListReportBWA2] @NODETEXT VARCHAR(80)
as
begin
set nocount on

/* hier ist die Deklaration der temp Tabelle @AliasTable */
declare @AliasTable Table (
  Alias VARCHAR(80), 
  Member_ID int,
  Alias_ID int
)


/* das Füllen der Tabelle klappt prima*/
insert into @Aliastable
SELECT     hyp_planning.HSP_OBJECT.OBJECT_NAME AS Alias, hyp_planning.HSP_ALIAS.MEMBER_ID, hyp_planning.HSP_ALIAS.ALIAS_ID
FROM         hyp_planning.HSP_ALIAS INNER JOIN
                      hyp_planning.HSP_MEMBER ON hyp_planning.HSP_ALIAS.MEMBER_ID = hyp_planning.HSP_MEMBER.MEMBER_ID INNER JOIN
                      hyp_planning.HSP_OBJECT ON hyp_planning.HSP_ALIAS.ALIAS_ID = hyp_planning.HSP_OBJECT.OBJECT_ID
WHERE     (hyp_planning.HSP_ALIAS.ALIASTBL_ID = 14)



declare @ChildID int
declare @Table Table ( 
  ID  INT, 
  Position VARCHAR(80), 
  Alias VARCHAR(80),
  ParentID INT, 
  GENERATION INT,
  HAS_CHILDREN INT,
  DIM_ID INT,
  RELPOSITION INT
) 
-- 1.Zeile in die Tabelle 
-- 

insert into @Table 
 SELECT     hyp_planning.HSP_OBJECT.OBJECT_ID, hyp_planning.HSP_OBJECT.PARENT_ID, hyp_planning.HSP_OBJECT.OBJECT_NAME, 

/* hier ist das Problem 
es wird folgender Fehler angezeigt

Meldung 137, Ebene 15, Status 2, Prozedur ListReportBWA2, Zeile 39
Die "@Aliastable"-Skalarvariable muss deklariert werden.
*/
@Aliastable.ALIAS, 

                      hyp_planning.HSP_OBJECT.GENERATION, hyp_planning.HSP_OBJECT.POSITION, hyp_planning.HSP_OBJECT.HAS_CHILDREN
FROM         hyp_planning.HSP_MEMBER INNER JOIN
                       @AliasTable ON hyp_planning.HSP_MEMBER.MEMBER_ID = @AliasTable.MEMBER_ID RIGHT OUTER JOIN
                      hyp_planning.HSP_OBJECT ON hyp_planning.HSP_MEMBER.MEMBER_ID = hyp_planning.HSP_OBJECT.OBJECT_ID
WHERE     (hyp_planning.HSP_OBJECT.OBJECT_NAME = @NODETEXT)

[NormanNG]

Hi,

du muss der temp.Tabelle einen Aliasnamen geben und damit arbeiten:

SQL-Code:

SELECT     
  hyp_planning.HSP_OBJECT.OBJECT_ID, hyp_planning.HSP_OBJECT.PARENT_ID, hyp_planning.HSP_OBJECT.OBJECT_NAME,
  A.ALIAS,  -- <-- Alias der Tabelle = A
  hyp_planning.HSP_OBJECT.GENERATION, hyp_planning.HSP_OBJECT.POSITION, hyp_planning.HSP_OBJECT.HAS_CHILDREN
FROM
  hyp_planning.HSP_MEMBER 
INNER JOIN @AliasTable A   -- <---- Alias der Tabelle = A 
  ON hyp_planning.HSP_MEMBER.MEMBER_ID = A.MEMBER_ID -- <-- hier nochmal ...
RIGHT OUTER JOIN hyp_planning.HSP_OBJECT 
  ON hyp_planning.HSP_MEMBER.MEMBER_ID = hyp_planning.HSP_OBJECT.OBJECT_ID
WHERE (hyp_planning.HSP_OBJECT.OBJECT_NAME = @NODETEXT)

[Jens Schumann]

Hallo,
super Tip - vielen Dank