Question about temporary table 1

[aspvbnetnerd]

I am trying to understand temporary tables that begins with #

With a simple query the creates a query and drops a query. This work Okay.

[COLOR=blue]CREATE[/color] [COLOR=blue]TABLE[/color] [#TEMPTABLE](
		[CardboardID] [[COLOR=blue]int[/color]] NOT NULL,
		[BuntchID] [[COLOR=blue]int[/color]] [COLOR=blue]IDENTITY[/color](1,1) NOT NULL, 
		[FirstSerialNumber] [[COLOR=blue]int[/color]] NULL, 
		[LastSerialNumber] [[COLOR=blue]int[/color]] NULL, 
		[ScanningDate] [[COLOR=#FF00FF]datetime[/color]] NULL, 
		[[COLOR=#FF00FF]Year[/color]] [nvarchar](2) NULL, 
		[DayNumber] [[COLOR=blue]smallint[/color]] NULL, 
		[ScanningStationID] [[COLOR=blue]smallint[/color]] NULL, 
		[Created] [[COLOR=#FF00FF]datetime[/color]] NULL [COLOR=blue]DEFAULT[/color] ([COLOR=#FF00FF]GETDATE[/color]()), 
		[Lost] [[COLOR=blue]bit[/color]] NULL)

[COLOR=blue]DROP[/color] [COLOR=blue]TABLE[/color] #TEMPTABLE

Tried the same but creating two procedure. One for creating a table and one for dropping a table

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] [dbo].[CreateTable]

[COLOR=blue]As[/color] 

[COLOR=blue]CREATE[/color] [COLOR=blue]TABLE[/color] [#TEMPTABLE](
		[CardboardID] [[COLOR=blue]int[/color]] NOT NULL,
		[BuntchID] [[COLOR=blue]int[/color]] [COLOR=blue]IDENTITY[/color](1,1) NOT NULL, 
		[FirstSerialNumber] [[COLOR=blue]int[/color]] NULL, 
		[LastSerialNumber] [[COLOR=blue]int[/color]] NULL, 
		[ScanningDate] [[COLOR=#FF00FF]datetime[/color]] NULL, 
		[[COLOR=#FF00FF]Year[/color]] [nvarchar](2) NULL, 
		[DayNumber] [[COLOR=blue]smallint[/color]] NULL, 
		[ScanningStationID] [[COLOR=blue]smallint[/color]] NULL, 
		[Created] [[COLOR=#FF00FF]datetime[/color]] NULL [COLOR=blue]DEFAULT[/color] ([COLOR=#FF00FF]GETDATE[/color]()), 
		[Lost] [[COLOR=blue]bit[/color]] NULL)

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] [dbo].[DropTempTable]

[COLOR=blue]As[/color]

[COLOR=blue]DROP[/color] [COLOR=blue]TABLE[/color] #TEMPTABLE

The I try to execute the procedure

CreateTable
DropTempTable

And I am getting an error that says
Cannot drop the table '#TEMPTABLE, because it does not exist or you do not have permission.

My question is this.

How can I make the second procedure find the #TEMPTABLE?

 

[CaptainD]

If you are using SQL Server 2000 or above you can use @ to create a table in memory that gets droped when the procedure goes out of scope.

DECLARE @tTemp TABLE 
( 
    id INT, 
    name VARCHAR(32) 
)

 

[aspvbnetnerd]

I know that. But as I said before it is two different procedures.
One that creates the temporary table and one the drops the table.

 

[CaptainD]

Here is another example from a storedProc I use

	-- Create temp table to hold the final values
	DECLARE @tFinal TABLE (iID INT IDENTITY(1,1)
		,fkStationSelectionID INT
		,fkPersonnelID INT DEFAULT(0)
		,iChoiceOrder CHAR(2)
		,sUnit Char(25)
		,sRank Char(25)
		,sShift Char(1)
		,iSortOrder INT DEFAULT(1))

 

[ca8msm]

Temporary tables have a certain "scope" in which they can be used. One created with a single hash (create table #temp) will only be available lovally, whereas one created with two hashes (create table ##temp) will be available globally. I think that's what you are alluding to.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[aspvbnetnerd]

ca8msm, Thank you very much.
I learnt something new today.

George

 

[chrissie1]

Temporary tables have a certain "scope" in which they can be used. One created with a single hash (create table #temp) will only be available lovally, whereas one created with two hashes (create table ##temp) will be available globally. I think that's what you are alluding to.

Lovally?? What were you thinking Mark?




Christiaan Baes
Belgium

My Blog

 

[aspvbnetnerd]

I think Mark meant Locally. Is the belgian guy back ;-)

 

[ca8msm]

I get confused easily


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLDenis]

run this

CREATE TABLE [#TEMPTABLE](
        [CardboardID] [int] NOT NULL,
        [BuntchID] [int] IDENTITY(1,1) NOT NULL, 
        [FirstSerialNumber] [int] NULL, 
        [LastSerialNumber] [int] NULL, 
        [ScanningDate] [datetime] NULL, 
        [Year] [nvarchar](2) NULL, 
        [DayNumber] [smallint] NULL, 
        [ScanningStationID] [smallint] NULL, 
        [Created] [datetime] NULL DEFAULT (GETDATE()), 
        [Lost] [bit] NULL)


exec DropTempTable

don't be confused now

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions