Solved

Error 2714 when parsing stored procedure

Posted on 2004-08-02
8
500 Views
Last Modified: 2008-02-01
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?
0
Comment
Question by:patrikt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 11692343
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
 
LVL 12

Author Comment

by:patrikt
ID: 11692388
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
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 11692417
Can you post full scripts here?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 5

Accepted Solution

by:
hkamal earned 250 total points
ID: 11693002
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
 
LVL 5

Expert Comment

by:hkamal
ID: 11693013
I said Sybase when I meant SQL SERVER !
Works for both actually
0
 
LVL 7

Expert Comment

by:FDzjuba
ID: 11695127
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
 
LVL 2

Expert Comment

by:nexusSam
ID: 11695304
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
 
LVL 12

Author Comment

by:patrikt
ID: 11695412
Hkamal is right.

It it realy bug of parser. Parser hates "double" creating temp table in different branches of IF.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question