• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

Stored proc not getting created because temp table already exists.

Using Sql Server 2012

I am using the following code where ever I am creating a temp table #wt3 in my stored proc code. But when I hit execute to create this SP I get the error mentioned below, at the second instance of creating this temp table:

IF (OBJECT_ID('tempdb..#wt3')) IS NOT NULL
			DROP TABLE #wt3	
SET ANSI_WARNINGS OFF
 SELECT TOP 0 * INTO #wt3 FROM db_template.dbo.DimTime

Open in new window


Error:
Msg 2714, Level 16, State 1, Procedure rollup_scores, Line 1463
There is already an object named '#wt3' in the database.

What am I missing?

Thank you.
0
patd1
Asked:
patd1
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Hmm... works on my SQL 2012 box every time.  Just for kicks and giggles, remove the extra parentheses marks, and add the begin and end, and see if that makes a difference.
IF OBJECT_ID('tempdb..#wt3') IS NOT NULL
   begin
   DROP TABLE #wt3	
   end

CREATE TABLE #wt3 (id int) 
INSERT INTO #wt3 (id) VALUES (7)
SELECT * FROM #wt3

Open in new window

Any chance this error is occuring somewhere else (Line 1463 is a long ways down)?
Show us the entire SQL code.

Also, I wonder if there is a SET statement somewhere that controls this behavior.
0
 
patd1Author Commented:
It wasn't working with Create SP, but worked with Alter sP.
What I did was named my temp table as #wt3_p while i was creating the SP.
Then went back in and removed the _p, so now I am doing an Atler SP with temp table named as #wt3 and it worked.

I guess there is some bug in create SP.
0
 
patd1Author Commented:
more research.
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now