Ist eine rekursiv aufgerufene gespeicherte Prozedur möglich, in SQL-Server?
Hier ist, was ich als VBScript-Subroutine:
sub buildChildAdminStringHierarchical(byval pAdminID, byref adminString)
set rsx = conn.execute ("select admin_id from administrator_owners where admin_id not in (" & adminString & ") and owner_id = " & pAdminID)
do while not rsx.eof
adminString = adminString & "," & rsx(0)
call buildChildAdminStringHierarchical(rsx(0),adminString)
rsx.movenext
loop
end sub
Ist es trotzdem, diesen in eine gespeicherte Prozedur, da es ja den rekursiven Aufruf des Unterprogramms?
Hier ist, was ich versucht habe...
CREATE PROCEDURE usp_build_child_admin_string_hierarchically
@ID AS INT,
@ADMIN_STRING AS VARCHAR(8000),
@ID_STRING AS VARCHAR(8000) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @index int;
DECLARE @length int;
DECLARE @admin_id int;
DECLARE @new_string varchar(8000);
SET @index = 1;
SET @length = 0;
SET @new_string = @ADMIN_STRING;
CREATE TABLE #Temp (ID int)
WHILE @index <= LEN(@new_string)
BEGIN
IF CHARINDEX(',', @new_string, @index) = 0
SELECT @length = (LEN(@new_string) + 1) - @index;
ELSE
SELECT @length = (CHARINDEX(',', @new_string, @index) - @index);
SELECT @admin_id = CONVERT(INT,SUBSTRING(@new_string, @index, @length));
SET @index = @index + @length + 1;
INSERT INTO #temp VALUES(@admin_id);
END
DECLARE TableCursor CURSOR FOR
SELECT Admin_ID FROM Administrator_Owners WHERE Admin_ID NOT IN (SELECT ID FROM #temp) AND Owner_ID = @ID;
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @admin_id;
WHILE @@FETCH_STATUS = 0
BEGIN
IF LEN(@ID_STRING) > 0
SET @ID_STRING = @ID_STRING + ',' + CONVERT(VARCHAR, @admin_id);
ELSE
SET @ID_STRING = CONVERT(VARCHAR, @admin_id);
EXEC usp_build_child_admin_string_hierarchically @admin_id, @ID_STRING, @ID_STRING;
FETCH NEXT FROM TableCursor INTO @admin_id;
END
CLOSE TableCursor;
DEALLOCATE TableCursor;
DROP TABLE #temp;
END
GO
Aber ich bekomme die folgende Fehlermeldung, wenn Sie die gespeicherte Prozedur aufgerufen wird...
Ein cursor mit dem Namen 'TableCursor' existiert bereits.
Ich vermute, dein Fehler tritt auf, weil der rekursive Aufruf erfolgt, bevor der cursor
Das problem ist nicht die Rekursion, das ist sicherlich erlaubt (msdn.microsoft.com/en-us/library/aa175801(SQL.80).aspx), ist es, dass Sie einen statischen cursor-Namen. Ich weiß nicht genug über cursors in MS-SQL-Server, post dies als eine Antwort, obwohl, da wäre es zu sagen, wie man einen cursor, verwenden Sie in dieser situation nützlich zu sein! 🙂
TableCursor
geschlossen ist. Wäre es möglich, den cursor einen dynamischen Namen (vielleicht TableCursorN
, wobei N die Tiefe der Rekursion - das müssten Sie machen, die als zusätzlichen parameter)?Das problem ist nicht die Rekursion, das ist sicherlich erlaubt (msdn.microsoft.com/en-us/library/aa175801(SQL.80).aspx), ist es, dass Sie einen statischen cursor-Namen. Ich weiß nicht genug über cursors in MS-SQL-Server, post dies als eine Antwort, obwohl, da wäre es zu sagen, wie man einen cursor, verwenden Sie in dieser situation nützlich zu sein! 🙂
InformationsquelleAutor Ryan | 2010-06-28
Du musst angemeldet sein, um einen Kommentar abzugeben.
Das problem ist, dass, während Ihr cursor ist nicht global, es ist eine Sitzung cursor. Da machst du die Rekursion, obwohl jeder iteration ist die Schaffung eines Cursors in einem neuen proc-Bereich, Sie sind alle in die gleiche PID (Verbindung) in der gleichen Zeit, so die Kollision.
Müssen Sie erzeugen einzigartige cursor-Namen in jeder iteration des Verfahrens basiert auf einigen Kriterien, die nicht reproduziert werden während der Rekursion.
Oder, vorzugsweise, einen Weg finden, das zu tun, was Sie brauchen, mit Logik, und behandeln alle erforderlichen Rekursion mit einer rekursiven CTE.
InformationsquelleAutor Toby
Können Sie eine
LOCAL
cursor, wie diese:Zumindest in SQL Server 2008 R2 (meine Maschine), kann auf diese Weise rekursiv aufrufen der sproc ohne "Cursor ist bereits vorhanden" Fehler.
Ich kann bestätigen, das funktioniert für 2005 zu. msdn.microsoft.com/en-us/library/ms180169(v=sql.90).aspx Diese Antwort sollte sein markiert als richtigen wirklich.
InformationsquelleAutor Blorgbeard
Können Sie, aber es ist in der Regel nicht eine gute Idee. SQL ist gemacht für set-basierte Operationen. Auch in MS SQL Server mindestens die Rekursion ist beschränkt auf die Anzahl der rekursiven Aufrufe, die es machen kann. Sie können nur nest bis zu 32 Ebenen tief.
Das problem in Ihrem Fall ist, dass der CURSOR geht über jeden Anruf, so dass Sie am Ende schaffen es mehr als einmal.
InformationsquelleAutor Tom H