Database

<br />
CREATE PROCEDURE spRecursiveGetChildren <br />
(<br />
    @ObjectId bigint, <br />
    @ObjectClassifier varchar(255),<br />
    @TempId varchar(36)<br />
)<br />
AS<br />
BEGIN<br />
	DECLARE @CursorName varchar(100)<br />
	DECLARE @SQL varchar(4000)<br />
	DECLARE @NSQL nvarchar(4000)<br />
	<br />
	-- Dynamic sql + cursor name because there'll be a recursive call and we'd run into CURSOR name conflict<br />
	SET @CursorName = ''spGetChildren_cursor_'' + CAST(@ObjectId as varchar(20)) + ''_'' + REPLACE(CAST(@ObjectClassifier AS varchar(70)), ''.'', ''_'')<br />
	<br />
	PRINT @CursorName<br />
						<br />
	INSERT INTO [dbo].[TMP_HierarchyMap] <br />
		(<br />
		ParentId,<br />
		ChildId,<br />
		ParentType,<br />
		ChildType,<br />
		...<br />
		TmpUID,<br />
		ReferencedId<br />
		)<br />
		SELECT <br />
            ParentId,<br />
            ChildId,<br />
            ParentType,<br />
            ChildType,<br />
            ...<br />
            @TempId,<br />
            Id<br />
            FROM HIERARCHYMAP M1<br />
            WHERE<br />
            ParentId = @ObjectId<br />
            AND<br />
            ParentType = @ObjectClassifier<br />
			AND<br />
			NOT EXISTS(SELECT 1 FROM TMP_HIERARCHYMAP M2 WHERE M1.ChildId=M2.ChildId AND M1.ChildType=M2.ChildType AND ParentId=@ObjectId AND ParentType=@ObjectClassifier AND TmpUID=@TempId)<br />
	<br />
	SET @SQL = <br />
		   ''DECLARE @ThisChildId bigint<br />
			DECLARE @ThisChildType varchar(255)<br />
			DECLARE @CountChildren int<br />
			<br />
		    DECLARE $CURSOR_NAME$ CURSOR FOR <br />
			SELECT ChildId,ChildType <br />
			FROM HIERARCHYMAP <br />
			WHERE  <br />
				ParentId = $OBJECTID$<br />
				AND ParentType = ''''$OBJECTCLASSIFIER$''''<br />
				<br />
				OPEN $CURSOR_NAME$<br />
				FETCH $CURSOR_NAME$ INTO @ThisChildId, @ThisChildType<br />
					<br />
				WHILE @@FETCH_STATUS = 0<br />
				BEGIN		<br />
					<br />
					SELECT @CountChildren = count(1) FROM HIERARCHYMAP WHERE ParentId = @ThisChildId AND ChildType = @ThisChildType<br />
					IF (@CountChildren>0)<br />
						BEGIN<br />
						<br />
						-- ** Recursive call<br />
						EXEC spRecursiveGetChildren @ThisChildId, @ThisChildType, ''''$TEMPID$''''<br />
						END<br />
					FETCH $CURSOR_NAME$ INTO @ThisChildId, @ThisChildType<br />
   				END<br />
					<br />
				CLOSE $CURSOR_NAME$<br />
				DEALLOCATE $CURSOR_NAME$<br />
				''<br />
		SET @SQL = REPLACE(@SQL, ''$CURSOR_NAME$'', @CursorName)<br />
		SET @SQL = REPLACE(@SQL, ''$OBJECTID$'', CAST(@ObjectId as varchar(20)) )<br />
		SET @SQL = REPLACE(@SQL, ''$OBJECTCLASSIFIER$'', CAST(@ObjectClassifier as varchar(70)) )<br />
		SET @SQL = REPLACE(@SQL, ''$TEMPID$'', CAST(@TempId as varchar(36)) )<br />
		<br />
		SET @NSQL = CAST(@SQL AS nvarchar(4000))<br />
		<br />
		PRINT @NSQL<br />
		<br />
		-- Actually executing recursive call! This prevents me from repackage this as UDF instead.<br />
		EXEC sp_executesql @NSQL<br />
END<br />

SET @SQL =
       'DECLARE @ThisParentId bigint;
        DECLARE @ThisParentType varchar(255);
        DECLARE @CountParent int;

        DECLARE $CURSOR_NAME$ CURSOR FOR
        SELECT ParentId,ParentType
        FROM HIERARCHYMAP
        WHERE
            ChildId = $OBJECTID$
            AND ChildType = ''$OBJECTCLASSIFIER$'';

            OPEN $CURSOR_NAME$;
            FETCH $CURSOR_NAME$ INTO @ThisParentId, @ThisParentType;

            WHILE @@FETCH_STATUS = 0;
            BEGIN

                SELECT @CountParent = count(1) FROM HIERARCHYMAP WHERE ChildId = @ThisParentId AND ChildType = @ThisParentType;
                IF (@CountParent>0)
                    BEGIN
                    EXEC spRecursiveGetParents @ThisParentId, @ThisParentType, ''$TEMPID$'';
                    END;
                FETCH $CURSOR_NAME$ INTO @ThisParentId, @ThisParentType;
            END;

            CLOSE $CURSOR_NAME$;
            DEALLOCATE $CURSOR_NAME$;
            '
    SET @SQL = REPLACE(@SQL, '$CURSOR_NAME$', @CursorName)
    SET @SQL = REPLACE(@SQL, '$OBJECTID$', CAST(@ObjectId as varchar(20)) )
    SET @SQL = REPLACE(@SQL, '$OBJECTCLASSIFIER$', CAST(@ObjectClassifier as varchar(70)) )
    SET @SQL = REPLACE(@SQL, '$TEMPID$', CAST(@TempId as varchar(36)) )

    PRINT @SQL

    EXECUTE @SQL