Skalarvariable muss deklariert werden

[Jens Schumann]

Hallo,
ich habe ein Problem in einer Stored-Procedure.
Ich möchte gerne innerhalb der Stored-Procedure Zwischenergebnisse in einer Tabellenvariablen speichern.
Grundsätzlich funktioniert das auch ganz gut. Nur kann ich nicht immer wie gewohnt auf die Variable zugreiffen.
 WHERE (hyp_planning.HSP_OBJECT.OBJECT_NAME =@tpe_upl_references.position) and An dieser Stelle verursacht folgende Fehlermeldung:

Code:

Meldung 137, Ebene 15, Status 2, Prozedur ListTree3, Zeile 43
Die "@tpe_upl_references"-Skalarvariable muss deklariert werden.

Wie kann ich das Problem lösen?

Hier die komplette Stored-Procedure

SQL-Code:

CREATE   Procedure [dbo].[ListTree3] @NODETEXT VARCHAR(80)
as
begin
set nocount on
declare @ChildID int

declare @tpe_upl_references Table
( 
  ID  INT, 
  PARENTID INT, 
  POSITION VARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
  ALIAS VARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
  HASCHILDREN INT,
  GENERATION INT,
  RELPOSITION INT
) 
-- 1.Zeile in die Tabelle 
-- 
insert into @tpe_upl_references 
  select object_ID, Parent_ID, object_name,null as alias, has_children, Generation,Position from hyp_planning.hsp_object 
    where (object_name = @NODETEXT) and  ((object_type=32) or (object_type=33) or (object_type=2)or (object_type=50))

Declare c Cursor local for select ID from @tpe_upl_references 
open c 
fetch next from c into @ChildID 
while @@Fetch_status = 0 begin 
-- 
-- Mit jedem Schleifendurchlauf werden in einem Abwasch ALLE Kindknoten eingefügt 
-- 
  insert into @tpe_upl_references 
    select object_ID,Parent_ID,object_name,null as alias,has_children,  Generation,Position from hyp_planning.hsp_object where Parent_ID = @ChildID 
  fetch next from c into @ChildID 
end 
close c 
deallocate c 


update @tpe_upl_references set alias= (SELECT HSP_OBJECT_1.OBJECT_NAME AS ALIAS
                          FROM   hyp_planning.HSP_OBJECT AS HSP_OBJECT_1 INNER JOIN
                                 hyp_planning.HSP_ALIAS ON HSP_OBJECT_1.OBJECT_ID = hyp_planning.HSP_ALIAS.ALIAS_ID RIGHT OUTER JOIN
                                 hyp_planning.HSP_OBJECT ON hyp_planning.HSP_ALIAS.MEMBER_ID = hyp_planning.HSP_OBJECT.OBJECT_ID
                          WHERE  (hyp_planning.HSP_OBJECT.OBJECT_NAME =@tpe_upl_references.position) and (hyp_planning.HSP_ALIAS.ALIASTBL_ID=14) )

set nocount off 
select * from @tpe_upl_references order by id,relposition


END

Ohne das Update funktioniert die Stored-Procedure mit der Tabellenvariablen einwandfrei. Wenn ich anstatt der Tabellenvariablen das Zwischenergebnis in die tempdb schreibe funktioniert es auch mit dem update tadellos.

[Jens Schumann]

Hallo,
leider konnte ich hier noch keine Lösung finden.

[alzaimar]

So vielleicht?

SQL-Code:

update @tpe_upl_references 
  set alias= HSP_OBJECT_1.OBJECT_NAME
from
    hyp_planning.HSP_OBJECT AS HSP_OBJECT_1 
INNER JOIN
    hyp_planning.HSP_ALIAS 
      ON HSP_OBJECT_1.OBJECT_ID = hyp_planning.HSP_ALIAS.ALIAS_ID 
RIGHT OUTER JOIN
  hyp_planning.HSP_OBJECT 
    ON hyp_planning.HSP_ALIAS.MEMBER_ID = hyp_planning.HSP_OBJECT.OBJECT_ID
WHERE  
    (hyp_planning.HSP_OBJECT.OBJECT_NAME =position) 
an  (hyp_planning.HSP_ALIAS.ALIASTBL_ID=14)

[Jens Schumann]

Hallo alzaimar,
vielen Dank für Deinen Tip.

SQL-Code:

update @tpe_upl_references  set alias= (SELECT  HSP_OBJECT_1.OBJECT_NAME AS ALIAS 
                          FROM   hyp_planning.HSP_OBJECT AS HSP_OBJECT_1 INNER JOIN 
                                 hyp_planning.HSP_ALIAS ON HSP_OBJECT_1.OBJECT_ID = hyp_planning.HSP_ALIAS.ALIAS_ID RIGHT OUTER JOIN 
                                 hyp_planning.HSP_OBJECT ON hyp_planning.HSP_ALIAS.MEMBER_ID = hyp_planning.HSP_OBJECT.OBJECT_ID 
                                 INNER JOIN @tpe_upl_references ON hyp_planning.HSP_OBJECT.OBJECT_NAME = position
                          WHERE (hyp_planning.HSP_ALIAS.ALIASTBL_ID=14) )

Das Statement erzeugt innerhalb der Stored Procedure beim Versuch eben diese anzulegen folgende Fehlermeldung

Code:

Meldung 209, Ebene 16, Status 1, Prozedur ListTree3, Zeile 43
Mehrdeutiger Spaltenname 'position'.

[mkinzler]

Du hast position auch nicht weiter spezifiziert. Gebe dort auch noch den Alias an

[Jens Schumann]

Zitat von mkinzler:

Du hast position auch nicht weiter spezifiziert. Gebe dort auch noch den Alias an

Ich weis aber nicht welchen Alias ich dort angeben soll.

Wenn ich einen Alias mit ... JOIN @tpe_upl_references tur ON ... in der Abfrage für @tpe_upl_references
angeben wird die Stored Procedure zwar kompiliert und bei Aufruf wird auch eine Ergebnismenge geliefert.
Aber leider mit einem Fehler beendet.

SQL-Code:

update @tpe_upl_references  set alias= (SELECT  HSP_OBJECT_1.OBJECT_NAME AS ALIAS 
                          FROM   hyp_planning.HSP_OBJECT AS HSP_OBJECT_1 INNER JOIN 
                                 hyp_planning.HSP_ALIAS ON HSP_OBJECT_1.OBJECT_ID = hyp_planning.HSP_ALIAS.ALIAS_ID RIGHT OUTER JOIN 
                                 hyp_planning.HSP_OBJECT ON hyp_planning.HSP_ALIAS.MEMBER_ID = hyp_planning.HSP_OBJECT.OBJECT_ID 
                                 INNER JOIN @tpe_upl_references tur ON hyp_planning.HSP_OBJECT.OBJECT_NAME =tur.position
                          WHERE (hyp_planning.HSP_ALIAS.ALIASTBL_ID=14) )

Hier die Feherlmeldung mit der die Stored Procedure beendet.

Code:

Meldung 512, Ebene 16, Status 1, Prozedur ListTree3, Zeile 39
Die Unterabfrage hat mehr als einen Wert zurückgegeben. Das ist nicht zulässig, wenn die Unterabfrage auf =, !=, <, <=, > oder >= folgt oder als Ausdruck verwendet wird.
Die Anweisung wurde beendet.

(466 Zeile(n) betroffen)

[alzaimar]

Dann liefert das SELECT wohl mehr als einen Wert zurück.

[Jens Schumann]

Zitat von alzaimar:

Dann liefert das SELECT wohl mehr als einen Wert zurück.

Das wird wohl stimmen.
Mit der tempdb funktioniert die Stored Procedure einwandfrei. Mit der Tabellenvariablen nicht.
Das verstehe ich nicht.

[alzaimar]

sehr eigenartig. Was liefern denn die SELECT Befehle für die einzelnen Zeilen? Vielleicht reicht ein 'SELECT TOP 1'?

[Jens Schumann]

mit

SQL-Code:

update @tpe_upl_references  set alias= (SELECT Top (1) HSP_OBJECT_1.OBJECT_NAME AS ALIAS 
                          FROM   hyp_planning.HSP_OBJECT AS HSP_OBJECT_1 INNER JOIN 
                                 hyp_planning.HSP_ALIAS ON HSP_OBJECT_1.OBJECT_ID = hyp_planning.HSP_ALIAS.ALIAS_ID RIGHT OUTER JOIN 
                                 hyp_planning.HSP_OBJECT ON hyp_planning.HSP_ALIAS.MEMBER_ID = hyp_planning.HSP_OBJECT.OBJECT_ID 
                                 INNER JOIN @tpe_upl_references tur ON hyp_planning.HSP_OBJECT.OBJECT_NAME =tur.position
                          WHERE (hyp_planning.HSP_ALIAS.ALIASTBL_ID=14) )

funktioniert es ohne Fehlermeldung.
Bin mir aber nicht sicher ob auch alle Datenensätze angezeigt werden.