gespeicherte Prozedur in View einbinden

Question

Hallo liebe Forumsmitglieder,
in einer rekursiven Prozedur wird ein ID ermittelt, welche ich später in einem View zu einer Tabelle verlinken möchte, um z.B. den Abteilungsleiter zu ermitteln.

Die Prozedur ist folgendermaßen aufgebaut und funktioniert auch soweit:

USE [dbUser]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Volker S.
-- Create date: 23.07.2015
-- Description:	Gibt die UserID und AbteilungsID des Abteilungsleiters einer Abteilung zurück
-- =============================================
ALTER PROCEDURE [dbo].[prGetALID]
	@AbteilungID int = 0
AS
BEGIN
	SET NOCOUNT ON;
	SET @AbteilungID = ISNULL(@AbteilungID,0)

	DECLARE @AbteilungArt int=0
	DECLARE @AbteilungParent int=0
	DECLARE @AbteilungChief int=0
	DECLARE @tAbteilungID int=0

	DECLARE curRS CURSOR LOCAL READ_ONLY FOR 
		SELECT ABTEILUNG_ART, PARENT_ID, ABTEILUNG_ID, ABTEILUNG_CHIEF FROM tbl_ABTEILUNG WHERE ABTEILUNG_ID = @AbteilungID
	
	OPEN curRS
	FETCH NEXT FROM curRS INTO @AbteilungArt, @AbteilungParent, @tAbteilungID, @AbteilungChief
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		IF  @AbteilungArt = 3 OR @AbteilungArt = 0
			BEGIN
				SELECT @AbteilungChief AS USER_ID, @tAbteilungID AS ABTEILUNG_ID
				BREAK
			END
		ELSE
			BEGIN
				EXEC prGetALID @AbteilungParent
			END
			FETCH NEXT FROM curRS INTO @AbteilungArt, @AbteilungParent, @tAbteilungID, @AbteilungChief
	END
	CLOSE curRS
	DEALLOCATE curRS
END

Das Problem ist nun, diese ID in einen View zu bekommen. Soweit ich mich belesen habe, ist das nur über eine Inlinefunktion oder eine Tabellenwertfunktion realisierbar. Da bisher alle versuche kläglich scheiterten, wende ich mich jetzt hilfesuchend an diese Stelle, in der Hoffnung auf Hilfe, wie ich den Code umbauen muss, dass ich die ID von besagter Funktion zurückgegeben bekomme.

Das einfachste wäre, so meine Idee, die Prozedur in der Inlinefunktion mit Parameter aufzurufen und diesen an die gespeicherte Prozedur durchzureichen.

Also so ungefähr:

SELECT * FROM EXEC(dbo.prGetALID(@AbteilungID))

Das funktioniert leider nicht, da die Fehler 'Falscher Syntax in der Nahe des EXEC-Schlüsselworts' generiert wird.

Hat denn evtl. jemand einen Vorschlag?

Vielen Dank.

 

---

Viele Grüße, Volker

Answer 1

Hallo Volker,

in einer View kannst Du keine Stored Procedure ausführen, in einer Inlinefunktion allerdings auch nicht. Da musst Du die SP selbst so umschreiben, das daraus eine Funktion wird, siehe: Neuerstellen von gespeicherten Prozeduren als Funktionen

Oder überarbeite Dein Konstrukt so, das Du auf die SP mit dem Cursor ganz verzichten kannst, da sollte man auch eine Set-Based Lösung finden lassen.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Hallo Olaf,
danke für deine Hilfe.
Ich hab mich für eine Tabellenwertfunktion entschieden, da ich ehrlich gesagt nicht weiß, was du mit einer Set-Based Lösung meinst. ;)

USE [dbUser]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Volker S.
-- Create date: 27.07.2015
-- Description:	gibt die AbteilungsID und die UserID des Abteilungssleiters einer untergeordneten Abteilung zurück
-- =============================================
ALTER FUNCTION [dbo].[fn_GetALID] 
(
	@AbteilungID int
	)
RETURNS 
@tbl_ALID TABLE 
(
	-- Add the column definitions for the TABLE variable here
	AL_ABT_ID int,
	AL_ID int
)
AS
BEGIN
	SET @AbteilungID = ISNULL(@AbteilungID,0)

	DECLARE curRS CURSOR READ_ONLY FOR
		SELECT ABTEILUNG_ART, PARENT_ID, ABTEILUNG_ID, ABTEILUNG_CHIEF FROM tbl_ABTEILUNG WHERE ABTEILUNG_ID = @AbteilungID

	DECLARE @AbteilungArt int=0
	DECLARE @AbteilungParent int=0
	DECLARE @AbteilungChief int=0
	DECLARE @tAbteilungID int=0
	DECLARE @blMatch bit=0
	
	OPEN curRS
	FETCH NEXT FROM curRS INTO @AbteilungArt, @AbteilungParent, @tAbteilungID, @AbteilungChief

	WHILE @blMatch=0
	BEGIN
		IF  @AbteilungArt = 3 OR @AbteilungArt = 1 OR @AbteilungArt = 0
			BEGIN
				SET @blMatch=-1
				INSERT @tbl_ALID
				SELECT @tAbteilungID, @AbteilungChief
				BREAK
			END
		ELSE
			CLOSE curRS
			DEALLOCATE curRS
			DECLARE curRS CURSOR READ_ONLY FOR
				SELECT ABTEILUNG_ART, PARENT_ID, ABTEILUNG_ID, ABTEILUNG_CHIEF FROM tbl_ABTEILUNG WHERE ABTEILUNG_ID = @AbteilungParent
			OPEN curRS
			FETCH NEXT FROM curRS INTO @AbteilungArt, @AbteilungParent, @tAbteilungID, @AbteilungChief
			
	END
	CLOSE curRS
	DEALLOCATE curRS
	RETURN 
END

Vielen Dank nochmals für den Hinweis.

---

Viele Grüße, Volker