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

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 2

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

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.