Error 2714 when parsing stored procedure

I have stored procedure with several IF statements. In each IF I use SELECT .. INTO #temp but I get error 2714 (table already exists).
It is not true becouse only one IF will be run.

What can I do?
LVL 12
patriktAsked:
Who is Participating?
 
hkamalConnect With a Mentor Commented:
I had this and it is very frustrating. The best option :
1. If you're selecting the same columns, create the table upfront and use INSERT .. SELECT instead
2. If you're selecting out different columns/tables depending on the IF, use Dynamic SQL to create the table name; viz:
SELECT @sql="select * into #temptab from TableA"
EXEC (@sql)
Since Sybase doesn't see the select into at compile time, you avoid the error
0
 
Ryan ChongCommented:
Try Drop the temp table 1st if it's exist, like:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[YourTableName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[YourTableName]

...
0
 
patriktAuthor Commented:
No way. Table does'not exests. I put drop before all selects but still have error.

It's only parsing error. It should work correctly when run.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Ryan ChongCommented:
Can you post full scripts here?
0
 
hkamalCommented:
I said Sybase when I meant SQL SERVER !
Works for both actually
0
 
FDzjubaCommented:
after you have created temp table, don't forget to drop at the end of the sp, and before you start sp check that none of the temp tables exist and if they do drop them:

if object_id('#temp') is not null
begin
   drop table #temp
end
0
 
nexusSamCommented:
FDzjuba, neither is necessary.
SQL appends the session ID to the temp table name (hence the 11-char name limit compared with 32 for permenant tables).
They are automatically destroyed when you exit a proc in which they were created.
As for checking for their existence, I wouls simply use a new name.
0
 
patriktAuthor Commented:
Hkamal is right.

It it realy bug of parser. Parser hates "double" creating temp table in different branches of IF.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.