Why can't we drop table variable (@table) using syntax (drop table @table)?

Question

1.00/5 (2 votes)

See more:

I know that @table drops automatically. We need not worry about it. But I want reason, why can't we drop table variable explicitly?

What I have tried:

I have searched in Google but didn't find appropriate answer.

Posted 19-Nov-17 19:40pm

HarshadaBS

Updated 18-Nov-20 13:28pm

Add a Solution

Comments

Santosh kumar Pithani 20-Nov-17 1:58am

https://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server

Santosh kumar Pithani 20-Nov-17 1:59am

Don't post like this questions ones more...

HarshadaBS 9-Dec-17 9:47am

@santosh Kumar sir .. Why? I searched everywhere but everyone gives complicated answers which is difficult to understand. That's why I posted here

Santosh kumar Pithani 10-Dec-17 22:58pm

Variable tables(@table) is not scheme objects(tables,stored procedures..etc) to store permanently in Database so we cannot drop it.Its a very simple question to find answer easily. Example :-Declare @a int=12 --Here we can't drop a variable but we can read it very time in sql script.

Suvendu Shekhar Giri 20-Nov-17 2:42am

You can't DROP a table variable as it's still a variable. These are also not affected by TRANSACTIONS.

Laxmidhar tatwa technologies 21-Nov-17 3:00am

If the table associated with a forgein key and the values are stored in child table

you should not drop the table

HarshadaBS 9-Dec-17 9:45am

Thanks Suvendu sir and laxmidhar sir. It helped me to understand the reason of my question.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Answer 1 · 2017-11-21T03:40:00

Solution 1

As mentioned in the comments, it is a variable and cannot be dropped. It is not stored in the same place. The simple answer is "because it is a variable."

Posted 21-Nov-17 3:40am

ZurdoDev

Comments

HarshadaBS 9-Dec-17 9:46am

Yes. I got it.

Member 14996704 · Answer 2 · 2020-11-18T13:28:00

The explanation is quite simple as the solution. You cannot execute or get the variable recongnized because when you type drop table @yourcreatedvariable, you are actually trying to delete a temp table that I suppose it is not the case.

Try this

DECLARE @myvar NVCHAR(50), @dropStatement NVCHAR(100)
SET @myvar = 'yourTableName'
SET @dropStatement = 'drop table ' + @myvar

exec sp_sqlexec @dropStatement

Jose