Showing posts with label temporary. Show all posts
Showing posts with label temporary. Show all posts

Wednesday, March 21, 2012

How to use Sql Servers Temp table in vb.net

There r two types of temporary tables in sql servers
1) Local temporary table (#tablename)
2) Global temporary table(##tablename)

but when i m creating any temp table & using it in .NET it doesn't store all the data..
i am storing data one by one but only last inserted row is visible in that table...

any solution on that...

pls help

Thanks in advance...

Mukund TambeMake sure that you use one connection only, and that you do not close the connection between the inserts. Local temporary tables (#) are deleted when the connection through which they were created are closed. Global temporary tables (##) are deleted when the last connection referencing them are closed.|||Make sure that you use one connection only, and that you do not close the connection between the inserts. Local temporary tables (#) are deleted when the connection through which they were created are closed. Global temporary tables (##) are deleted when the last connection referencing them are closed.
let me check..

thanks a lot roac..|||You may also be a victim of "connection pooling" if you are running in an environment like ASP where you can have many threads sharing access to the same database. Each call to the database needs to stand on its own in a connection pooling environment, you can't depend on what may or may not have happened in a previous call to the database when you use connection pooling.

-PatP|||You have to let us know what you're doing...because it sounds dicey to begin with

Can you write a stored procedure?sql

Wednesday, March 7, 2012

How to use CTE instead of temp table

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

If you have the code working with temp tables consider keeping it with the temp tables. Currently, there are some optimizations you can make using temp tables that are not available with CTEs. I looked through this and need some help understanding it. Can you provide (1) some sample data and (2) the desired target output? This will go a long way toward providing help.

Also, give another look to the article in books online relating to CTEs. Also, you can string together multiple CTE definitions that can be used in concert with something like:

;with CTE#1

( select ...

), CTE#2

( select ...

), ...

), CTE#N

( ...

)

select ...

from <yourCTEs>