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.

Thanks for any help!

Answer 1

You can, but it's usually not a good idea. SQL is made for set-based operations. Also, in MS SQL Server at least, the recursion is limited to the number of recursive calls that it can make. You can only nest up to 32 levels deep.

The problem in your case is that the CURSOR lasts through each call, so you end up creating it more than once.

Answer 2

The problem is that while your cursor isn't global, it is a session cursor. Since you're doing recursion, even though each iteration is creating a cursor in a new proc scope, they're all being created in the same PID (connection) at the same time, thus the collision.

You'll need to generate unique cursor names in each iteration of the procedure based on some criteria that won't be reproduced during the recursion.

Or, preferably, find a way to do what you need using set logic, and handle any necessary recursion using a recursive CTE.