CREATE PROCEDURE {schema}[{objectQualifier}gs_Role_AlbumSelectAllAlbumsByRoleName]
(    @RoleName nvarchar(256), @GalleryId int )

AS
SET NOCOUNT ON

/* Retrieve all the album IDs that are affected by the specified role name. The album IDs that are stored in
the gs_Role_Album table only hold the highest ranking album ID. */

/* If the role is applied to the root album, then we can just return all albums in the gallery. This is
much cheaper than drilling down by album. */
DECLARE @RootAlbumId int
SELECT @RootAlbumId = AlbumId FROM {schema}[{objectQualifier}gs_Album] WHERE AlbumParentId = 0 AND FKGalleryId = @GalleryId

IF EXISTS (SELECT * FROM {schema}[{objectQualifier}gs_Role_Album] WHERE FKRoleName = @RoleName AND FKAlbumId = @RootAlbumId)
BEGIN
    SELECT AlbumId FROM {schema}[{objectQualifier}gs_Album] WHERE FKGalleryId = @GalleryId
END
ELSE
BEGIN
    /* The role applies to an album or albums below the root album, so we need to drill down and retrieve all
the children. Start by creating a temporary table to hold our data. */
    DECLARE @AlbumList table
        (AlbumId int not null,
        AlbumParentId int not null,
        AlbumDepth int not null)

    /* Insert the top level album IDs. */
    INSERT @AlbumList (AlbumId, AlbumParentId, AlbumDepth)
    SELECT FKAlbumId, 0, 1
    FROM {schema}[{objectQualifier}gs_Role_Album] ra INNER JOIN {schema}[{objectQualifier}gs_Album] a ON ra.FKAlbumId = a.AlbumId
    WHERE (ra.FKRoleName = @RoleName) AND (a.FKGalleryId = @GalleryId)

    /* Continue drilling down, level by level, until we reach a level where there are no more child albums. */
    WHILE (@@rowcount > 0) BEGIN
        INSERT @AlbumList (AlbumId, AlbumParentId, AlbumDepth)
        SELECT a.AlbumId, a.AlbumParentId, al.AlbumDepth + 1
        FROM {schema}[{objectQualifier}gs_Album] a JOIN @AlbumList al ON a.AlbumParentId = al.AlbumId
        WHERE al.AlbumDepth = (SELECT MAX(AlbumDepth) FROM @AlbumList)
    END

    /* Retrieve the list of album IDs. */
    SELECT AlbumId
    FROM @AlbumList
END

RETURN

private static IDataReader GetDataReaderRoleAllAlbums(string roleName)
{
    // Retrieve all the album IDs that are affected by the specified role name. The album IDs that are stored in
    // the gs_Role_Album table only hold the highest ranking album ID. If the role is applied to the root album,
    // then we can just return all albums in the gallery. If not, we need to drill down and retrieve all
    // the children. Follow these basic steps:
    // 1. Find out if the role applies to the root album. If it does, return all album ID's for the current gallery.
    // Otherwise, continue with next step.
    // 2. Create a temporary table to hold our working set of data.
    // 3. Insert the top level album IDs.
    // 4. Continue drilling down, level by level, until we reach a level where there are no more child albums.
    // 5. Retrieve the list of album IDs from the temporary table.
    DbTransaction tran = null;
    SQLiteConnection cn = GetDBConnectionForGallery();
    try
    {
        if (cn.State == ConnectionState.Closed)
            cn.Open();

        if (!IsTransactionInProgress())
            tran = cn.BeginTransaction();

        int galleryId = ConfigManager.GetGalleryServerProConfigSection().Core.GalleryId;

        using (SQLiteCommand cmd = cn.CreateCommand())
        {
            string sql = "SELECT EXISTS (SELECT * FROM gs_Role_Album ra INNER JOIN gs_Album a ON ra.FKAlbumId = a.AlbumId WHERE (a.AlbumParentId = 0) AND (ra.FKRoleName = @RoleName ) AND (a.FKGalleryId = @GalleryId))";
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@RoleName", roleName);
            cmd.Parameters.AddWithValue("@GalleryId", galleryId);

            bool roleAffectsAllAlbums = Convert.ToBoolean(cmd.ExecuteScalar());

            if (roleAffectsAllAlbums)
            {
                // The role affects all albums, so we can just get a list of all albums rather than drill down album by album.
                sql = "SELECT AlbumId FROM gs_Album WHERE FKGalleryId = @GalleryId";
                cmd.Parameters.Clear();
                cmd.CommandText = sql;
                cmd.Parameters.AddWithValue("@GalleryId", galleryId);

                if (IsTransactionInProgress())
                    return cmd.ExecuteReader();
                else
                {
                    // Commit the transaction if it's the one we created in this method.
                    if (tran != null)
                        tran.Commit();
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            else
            {
                // The role applies to an album or albums below the root album, so we need to drill down and retrieve all
                // the children. Start by creating a temporary table to hold our data.
                string tempTableName = "tmp" + Guid.NewGuid().ToString().Replace("-", String.Empty);
                sql = String.Format(@"
CREATE TEMP TABLE {0} (
[AlbumId] INTEGER NOT NULL,
[AlbumParentId] INTEGER NOT NULL,
[AlbumDepth] INTEGER NOT NULL);

INSERT INTO {0} (AlbumId, AlbumParentId, AlbumDepth)
SELECT FKAlbumId, 0, 1
FROM gs_Role_Album INNER JOIN gs_Album ON gs_Role_Album.FKAlbumId = gs_Album.AlbumId
WHERE (gs_Role_Album.FKRoleName = @RoleName) AND (gs_Album.FKGalleryId = @GalleryId);", tempTableName);
                cmd.Parameters.Clear();
                cmd.CommandText = sql;
                cmd.Parameters.AddWithValue("@RoleName", roleName);
                cmd.Parameters.AddWithValue("@GalleryId", galleryId);
                cmd.ExecuteNonQuery();

                sql = String.Format(@"
INSERT INTO {0} (AlbumId, AlbumParentId, AlbumDepth)
SELECT a.AlbumId, a.AlbumParentId, al.AlbumDepth + 1
FROM gs_Album a JOIN {0} al ON a.AlbumParentId = al.AlbumId
WHERE al.AlbumDepth = (SELECT MAX(AlbumDepth) FROM {0})
", tempTableName);
                cmd.Parameters.Clear();
                cmd.CommandText = sql;
                int numRows;
                do
                {
                    numRows = cmd.ExecuteNonQuery();
                } while (numRows > 0);

                sql = String.Format("SELECT AlbumId FROM {0};", tempTableName);
                cmd.CommandText = sql;

                if (IsTransactionInProgress())
                    return cmd.ExecuteReader();
                else
                {
                    // Commit the transaction if it's the one we created in this method.
                    if (tran != null)
                        tran.Commit();
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
        }
    }
    catch
    {
        if (tran != null)
            tran.Rollback();
        throw;
    }
    finally
    {
        if (tran != null)
            tran.Dispose();
    }
}