Common Table Expressions

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[core_sp_get_profile_member_Details]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[core_sp_get_profile_member_Details]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE                       PROC dbo.core_sp_get_profile_member_Details
@OrganizationID int,
@ProfileType int,
@OwnerID int,
@FirstName varchar(40),
@LastName varchar(40),
@ParentProfileID int

AS

CREATE TABLE #ChildProfiles(
    [profile_id] [int] NOT NULL)

-- Declare a cursor for determinining the groups and members in any child profiles
DECLARE @ProfileID int
DECLARE @ProfileOwnerID int

DECLARE ChildProfile CURSOR LOCAL FOR
    SELECT     profile_id,
        owner_id
    FROM core_profile
    WHERE organization_id = @OrganizationID
    AND profile_type = @ProfileType
    AND @ParentProfileID = -1 OR (parent_profile_id = @ParentProfileID OR profile_id = @ParentProfileID)

-- Open the cursor
OPEN ChildProfile

-- Fetch the first record
FETCH NEXT FROM ChildProfile
    INTO     @ProfileID,
        @ProfileOwnerID

-- Loop through all the records
WHILE @@FETCH_STATUS = 0
BEGIN

    IF @ProfileType <> 0 OR @ProfileOwnerID = @OwnerID
    BEGIN

        INSERT INTO #ChildProfiles
        VALUES (@ProfileID)

        -- For each child profile, call this stored procedure recursively to recurse down
        -- all child profiles.
        DECLARE @RC int
        EXEC @RC = core_sp_get_profile_member_Details_Recurse @ProfileID

    END

    -- Read the next record
    FETCH NEXT FROM ChildProfile
        INTO     @ProfileID,
            @ProfileOwnerID

END

-- Close and deallocate the cursor
CLOSE ChildProfile
DEALLOCATE ChildProfile

SELECT     *
FROM core_v_profile_memberList
WHERE profile_id in (SELECT profile_id FROM #ChildProfiles)
AND (nick_name like @FirstName + '%' OR first_name like @FirstName + '%')
AND (last_name like @LastName + '%')
ORDER BY person_name

DROP TABLE #ChildProfiles

USE [Arena]
GO
/****** Object:  StoredProcedure [dbo].[core_sp_get_profile_member_Details_Recurse]    Script Date: 04/29/2008 19:05:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER                  PROC [dbo].[core_sp_get_profile_member_Details_Recurse]
@ParentProfileID int

AS

-- Declare a cursor for determinining the groups and members in any child profiles
DECLARE @ProfileID int

DECLARE ChildProfile CURSOR LOCAL FOR
    SELECT     profile_id
    FROM core_profile
    WHERE isnull(parent_profile_id, -1) = @ParentProfileID

-- Open the cursor
OPEN ChildProfile

-- Fetch the first record
FETCH NEXT FROM ChildProfile
    INTO     @ProfileID

-- Loop through all the records
WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT INTO #ChildProfiles
    VALUES (@ProfileID)

    -- For each child profile, call this stored procedure recursively to recurse down
    -- all child profiles.
    DECLARE @RC int
    EXEC @RC = core_sp_get_profile_member_Details_Recurse @ProfileID

    -- Read the next record
    FETCH NEXT FROM ChildProfile
        INTO     @ProfileID

END

-- Close and deallocate the cursor
CLOSE ChildProfile
DEALLOCATE ChildProfile

USE [Arena]
GO
/****** Object:  StoredProcedure [dbo].[core_sp_get_profile_member_Details]    Script Date: 04/29/2008 18:57:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER                       PROC [dbo].[core_sp_get_profile_member_Details]
@OrganizationID int,
@ProfileType int,
@OwnerID int,
@FirstName varchar(40),
@LastName varchar(40),
@ParentProfileID int,
@Status varchar(500)

AS

    WITH cte_profiles (
        [profile_id] )
    AS
    (
        SELECT
           P.[profile_id]
        FROM core_profile P
        WHERE (ISNULL(P.parent_profile_id,-1) = @ParentProfileID OR P.profile_id = @ParentProfileID)
        AND P.organization_id = @OrganizationID
        AND P.profile_type = @ProfileType
        AND (P.profile_type <> 0 OR p.owner_id = @OwnerID)

        UNION ALL

        SELECT
           P.[profile_id]
        FROM core_profile P
        INNER JOIN cte_profiles CTE ON CTE.profile_id = ISNULL(P.parent_profile_id,-1)
        WHERE P.organization_id = @OrganizationID
        AND P.profile_type = @ProfileType
        AND (P.profile_type <> 0 OR p.owner_id = @OwnerID)
    )

    SELECT ML.*
    FROM cte_profiles P
    INNER JOIN core_profile_member PM
        ON PM.profile_id = P.profile_id
    INNER JOIN core_v_profile_memberList ML
        ON ML.profile_id = PM.profile_id
        AND ML.person_id = PM.person_id
    WHERE (ML.nick_name like @FirstName + '%' OR ML.first_name like @FirstName + '%')
    AND (ML.last_name like @LastName + '%')
    AND CAST(PM.status_luid as varchar) in ( SELECT item FROM dbo.fnSplit(@Status)  )
    ORDER BY ML.last_name, ML.nick_name