how can i drop a table which i have declared in my SP..

Question

like i declared a table...

declare @tempTable  TABLE
   (Id bigint identity(1,1),
   bKey int,
   DateT DateTime,
   Addres nvarchar(max),
   unit bigint)

and i want to drop it...but im stucked coz drop table n truncate are not working.

ani ideaaa...???

Answer 1

You don't need to drop table variables (i.e. those that start with @)

Answer 2

why do you want to drop a table variable? scope of table variable is in declare stored procedure only...

check this article... Table Variables In T-SQL

UPDATE

Use #temptable as suggested by @Adam... Then TRUNCATE TABLE #temptable, instead of DROP TABLE; this way no need to recreate it within loop.

Answer 3

You've declared the table as a table variable. It will only exist for the duration of the stored procedure. When the procedure ends, it is automatically "dropped" (ceases to exist is more accurate, as it never really existed in the way other tables do). You cannot drop them manually.

Answer 4

Table variables only live while they are in scope, in your case for the duration of the stored procedure. It will delete itself once the procedure completes.

You might be thinking of a temporary table:

CREATE TABLE #MyTempTable

There are differences between temporary tables and table variables:

http://sqlnerd.blogspot.com/2005/09/temp-tables-vs-table-variables.html

A better link:

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

Answer 5

You created a temporary table which is saved in a variable. It only exists as long as the store procedure is being executed. When the SP has finished the temporary table ceases to exists and it's being deleted automatically.

edit:

you could try testing it by running the stored procedure and then try to run a select query on your @tempTable