How to use CTE instead of temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to use CTE instead of temp table

Author

Topic

Jayaraj
Starting Member

3 Posts

Posted - 2007-04-25 : 21:02:59

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

Thanks,
Jay

herothecat
Starting Member

19 Posts

Posted - 2007-04-25 : 21:34:48

Jay,

First, I recomend reading BOL on CTE's.

Second, and more to your question. CTEs are dropped from memory after the next SQL statement is executed. If you need to keep them around, then I would suggest using table variables. They reside in memory for the scope of your proc and not the tempdb.

Why push the envelope when you can just open it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources