Is a recursively called stored procedure possible in SQL Server?

Question

Here is what I have as 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

Is there anyway to turn this into a stored procedure since it's got the recursive call in the subroutine?

Here is what I've tried...

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

But I get the following error when that stored procedure is called...

A cursor with the same name 'TableCursor' already exists.

Answer 1

You can specify a LOCAL cursor, like this:

DECLARE TableCursor CURSOR LOCAL FOR
SELECT ...

At least in SQL Server 2008 R2 (my machine), this allows you to recursively call the sproc without running into "Cursor already exists" errors.