Вот то, что я имею как Подпрограмма VBScript:
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
Там должен так или иначе превратить это в хранимую процедуру, так как она имеет рекурсивный вызов в подпрограмме?
Вот то, что я попробовал...
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
Но я получаю следующую ошибку, когда ту хранимую процедуру называют... Курсор с тем же именем 'TableCursor' уже существует.
Проблема в том, что, хотя ваш курсор не является глобальным, он является курсором сеанса. Поскольку вы выполняете рекурсию, даже если каждая итерация создает курсор в новой области действия, все они создаются в одном и том же PID (соединении) одновременно, поэтому возникает конфликт.
Вам нужно будет генерировать уникальные имена курсоров на каждой итерации процедуры на основе некоторых критериев, которые не будут воспроизводиться во время рекурсии.
Или, что лучше, найти способ делать то, что вам нужно, используя логику множества, и обрабатывать любую необходимую рекурсию с помощью рекурсивного CTE.
Можно, но обычно это не очень хорошая идея. SQL предназначен для операций на основе наборов. Кроме того, по крайней мере, в MS SQL Server рекурсия ограничена количеством рекурсивных вызовов, которые она может сделать. Глубина вложения может составлять не более 32 уровней.
Проблема в вашем случае в том, что CURSOR длится каждый вызов, поэтому вы в конечном итоге создаете его более одного раза.