I am trying to use CTE (Common Table Expressions) instead of Temporary tables. First constraint I felt was we need to SELECT that CTE out as soon as we create it. The second constraint is, it says CTE as invalid after certain points (or batches ) on the code. It will be great if you can give a workaround. This is my procedure.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

alter PROC [dbo].[spDL_Menu_get_temp]
        @user_id VARCHAR(2000) ,
        @request_root VARCHAR(255) ,
        @request_query_string VARCHAR(255)
    AS

/*


*/

DECLARE @query_string_local AS VARCHAR(255)

SET @query_string_local =
        CASE WHEN (LTRIM(RTRIM(@request_query_string)) = '') THEN '?'
            WHEN (LTRIM(RTRIM(@request_query_string)) <> '') THEN ('?' + @request_query_string)
        END
;

----------------------------------------------------------------------------------------------------------------------------------------
WITH TBL_MENUS( TABLE_ID , MENU_ID , MENU_NAME , MENU_LEVEL , MENU_ID_PARENT , MENU_LEVEL_INDEX, URL_TARGET) AS
    (
        SELECT MEN.table_id ,
                MEN.menu_id ,
                MEN.menu_name ,
                MEN.menu_level ,
                MEN.menu_id_parent ,
                MEN.menu_level_index ,
-- (@request_root + url_target + '?' + @request_query_string ) AS url_target
                REPLACE( (@request_root + url_target + @query_string_local + ISNULL(query_string,'')) , '??','?') AS url_target
            FROM DL_Menu AS MEN
                JOIN DL_MENU_PERMISSIONS PER ON MEN.menu_id = PER.menu_id
                JOIN DL_USER USR ON USR.USERID = @user_id AND PER.role_id = USR.role

    )

 SELECT TOP 1 MENU_ID FROM TBL_MENUS ;


WITH TBL_ROLES( ROLE ) AS
    (
        SELECT ROL.ROLE_DESC AS ROLE FROM DL_USER USR
            LEFT OUTER JOIN DL_USERROLE ROL ON USR.ROLE = ROL.ROLE_ID
            WHERE USERID = @USER_ID
    )

-- select * from dl_userrole
 SELECT MEN.MENU_ID , ROL.ROLE FROM TBL_ROLES ROL , TBL_MENUS MEN
 SELECT top 1 role FROM TBL_ROLES

------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @MENU_LEVEL_INDEX AS TINYINT
--SELECT @MENU_LEVEL_INDEX = MAX(MENU_LEVEL_INDEX) FROM TBL_MENUS WHERE MENU_LEVEL = 1
SELECT @MENU_LEVEL_INDEX
SET @MENU_LEVEL_INDEX = @MENU_LEVEL_INDEX + 1


---------------------------------------------------------------------------------------------------------------------------------------
SELECT TABLE_ID , MENU_ID , MENU_NAME , MENU_LEVEL , MENU_ID_PARENT , MENU_LEVEL_INDEX, URL_TARGET FROM TBL_MENUS
---------------------------------------------------------------------------------------------------------------------------------------
    UNION
        SELECT 'Roles', 'Roles' , 'My Roles' , 1 , NULL , @MENU_LEVEL_INDEX ,
                                    REPLACE( (@request_root + url_target + @query_string_local + ISNULL(query_string,'')) , '??','?') AS url_target
---------------------------------------------------------------------------------------------------------------------------------------
        UNION
        SELECT ROL.ROLE , ROL.ROL , ROL.ROLE , 2 , 'Roles' , ROL.ROL ,
            (REPLACE( (@request_root + url_target + @query_string_local + ISNULL(query_string,'')) , '??','?') + '?dbrole=' + ROL.ROLE) AS url_target
-------------------------------------------------------------------------------------------
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO