Solved

Error 2714 when parsing stored procedure

Posted on 2004-08-02
8
494 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
View SQL database for NPS accounting 4 23
Incorrect syntax near the keyword 'INNER' 3 38
SQL: get ride of blank rows 11 23
sql server string_split 4 28
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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