[T-SQL] Table variable is not automatically dropped in TempDB

---

Table variable is not automatically dropped in TempDB Posted: 27 Apr 2013 06:50 AM PDT hi,I have created procedure like .USE [master]GO/****** Object: StoredProcedure [dbo].[b] Script Date: 04/28/2013 01:09:39 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo].[b]as begindeclare @i table (id int)select * From tempdb.sys.tablesendand after i ran the executed my proceexec bThe result is :name objectid#023D5A04 37575172 and so on...i closed the above connection and after i opened new connection and ran the query likeselect * from tempdb.sys.tablesit is showing the below result and table exists in tempdb with the name:The result is :name objectid#023D5A04 37575172 and so on...Question is:Why did not it automatically dropped when i ran procedure and even i closed the connection please help on this..Thanks,Dastagiri

get max Posted: 27 Apr 2013 06:52 AM PDT CREATE TABLE [dbo].[Table_A]( [id] [smallint] NULL, [name] [varchar](50) NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table_B]( [ID] [smallint] NULL, [DATE] [date] NULL, [ORDER] [varchar](50) NULL) ON [PRIMARY]GOINSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (1,'Name1')GOINSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (2,'Name2')GOINSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (3,'Name3')GOINSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (4,'Name4')GOINSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (5,'Name5')GOINSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (6,'Name6')GOINSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (7,'Name7')GOINSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (8,'Name8')GOINSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (9,'Name9')GOINSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (10,'Name10')GOINSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (1,'2013-1-2','a')GOINSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (2,'2013-1-2','a')GOINSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (3,'2013-1-2','a')GOINSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (4,'2013-1-2','a')GOINSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (5,'2013-1-2','a')GOINSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (1,'2013-1-20','b')GOINSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (2,'2013-1-20','b')GOINSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (3,'2013-1-20','b')GOINSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (4,'2013-1-20','b')GOINSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (5,'2013-1-20','b')GOINSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (1,'2013-2-20','c') GOINSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (2,'2013-2-20','c')GOINSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (3,'2013-2-20','c')GOHow do I do something like this?select a.name, b.[DATE], b.[ORDER]from dbo.Table_A a left outer join dbo.Table_B b on a.id = b.ID and b.[DATE] = (select MAX([date]) from dbo.Table_B b inner join dbo.Table_A a on b.ID = a.id)This is the result I need.Name1 2/20/2013 cName2 2/20/2013 cName3 2/20/2013 cName4 1/20/2013 bName5 1/20/2013 bName6 NULL NULLName7 NULL NULLName8 NULL NULLName9 NULL NULLName10 NULL NULL

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610