CODEWITHSUNDEEP
sql
Tami
Tami

Reputation: 586

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

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...???

Upvotes: 5

Views: 7078

Answers (6)

Ernest
Ernest

Reputation: 1468

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

DELETE FROM @tempTable

Upvotes: 0

Sandeep Kumar M
Sandeep Kumar M

Reputation: 3851

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.

Upvotes: 2

Rob
Rob

Reputation: 6891

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

Upvotes: 2

Adam Houldsworth
Adam Houldsworth

Reputation: 64537

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

Upvotes: 9

Ant
Ant

Reputation: 4928

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.

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300769

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

Upvotes: 6

Related Questions