CODEWITHSUNDEEP
sqlsql-serveride
Konrad Viltersten
Konrad Viltersten

Reputation: 39250

How to calm the IDE about not declared temporary table?

When I design a temporary table as follows, the manager growls at the name #Temp and marks it with red highlight.

drop table #Temp
select * into #Temp
from Donkeys

When I hover over the highlight, the reason is - as expected - that the name isn't recognized.

Cannot drop the table '#Temp', because it does not exist or you do not have permissions.

Now, I'm not a SQL developer - I come from C# and I'm spoiled by intellisense, Resharper and what not, so I dislike when something is highlighted (even although it works). I installed Management Studio 11.x just to get the intellisense working and I want to get my money worth, if possible.

The question is - can I do something about the highlight (purely visually, because the functionality is - as pointed out earlier - as it's supposed to)?

Please note that the question is not about why it happens or if it's a problem. I do understand perfectly well why and I'm declaring it to be a problem (yeah, I admit it's not the biggest issue but it's big enough for me to actually invest time asking). Also, I'm human (i.e. lazy-ish) so a simple solution will do. :)

Upvotes: 0

Views: 80

Answers (3)

Christian Barron
Christian Barron

Reputation: 2755

It can be achieved by the combination ctrlshiftR.

You are receiving this issue because #temp does not exist yet. You either need to check for it's existence like so:

if OBJECT_ID('tempdb..#temp') is not null
begin
drop table #temp
end
select * into #Temp
from Donkeys

or you can just drop the table after you've used it:

select * into #Temp
from Donkeys
Drop table #Temp

Upvotes: 1

Mihir Shah
Mihir Shah

Reputation: 988

use like this way :-

If Object_Id('tempdb.dbo.#Temp') Is Not Null Drop Table #Temp;

select * into #Temp
from Donkeys

Upvotes: 2

Tikkes
Tikkes

Reputation: 4689

You need to check if the table exists before trying to drop it of course. Otherwise the IDE will keep on giving you red lines.

if exists(select 1 from tempdb.sys.tables where object_id = object_id('tempdb..#Temp'))
drop table #Temp

select * into #Temp
from Donkeys

It will also be better to rerun your script without having to select individual steps.

Upvotes: 1

Related Questions