How can I delete the table that I declared in my SP

Question

as i declared the table ...

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

and I want to abandon it ... but I am stuck in coz drop table n truncate not working.

ani ideaaa ... ???

Answer 1

Variable tables only live if they are in scope, in your case, during the stored procedure. He will remove himself after completion of the procedure.

Perhaps you are 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

The best link:

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

Answer 2

You do not need to drop table variables (i.e. those starting with @)

Answer 3

why do you want to delete a table variable? the scope of the table variable is specified only in the stored procedure ...

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

UPDATE

Use #temptable as suggested by @ Adam ... Then TRUNCATE TABLE #temptable instead of DROP TABLE; thus, there is no need to recreate it in a loop.

Answer 4

You have declared the table as a table variable. It will exist only for the duration of the stored procedure. When the procedure ends, it is automatically “discarded” (it ceases to exist more precisely, since it never existed in the way other tables do it). You cannot delete them manually.

Answer 5

A temporary table has been created and stored in a variable. It exists only as long as the repository procedure is running. When the SP completes, the temporary table ceases to exist and is automatically deleted.

edit:

you can try testing it by running the stored procedure and then try running the select query on @tempTable

Answer 6

In your SP, this will remove all values ​​from the declared table

 DELETE FROM @tempTable