Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How tdo I branch out in creation of a temp table in a stored procedure

Posted on 2008-10-20
4
Medium Priority
?
276 Views
Last Modified: 2010-04-21
I have a stored proc that I would like to create a temporary table with n or n + 2 columns based on a certain if condition

Currently it gives me an error
There is already an object named '#ActivityInfo1' in the database, when I excute the creation of the proc, it does not excute succesfully. Please note that I am just executing the SQL to compile successfully.
IF @Include >= 1   
		BEGIN	
		    CREATE TABLE #ActivityInfo1 
		    (    
		      tID     int    
		    , TradingArea   char(4)   not null    
		    , TraderID    varchar(4)  null  
                      , IncludeArea  varcahr(10) null
                      , IncludeRegion  varchar(20) null  
		    )
                   END
                 ELSE
                   BEGIN
                           CREATE TABLE #ActivityInfo1 
		    (    
		      tID     int    
		    , TradingArea   char(4)   not null    
		    , TraderID    varchar(4)  null    
		    ) 
                   END

Open in new window

0
Comment
Question by:countrymeister
[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
4 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22762918
Make sure the table is dropped.
DROP TABLE #ActivityInfo1
It's probably hanging around in memory. Be sure your Stored Procedure includes a drop as well. If the Stored Procedure is recognizing two Create statments then you may need to put them in a string and EXECUTE the string.
EXECUTE strCreateTable
0
 
LVL 1

Author Comment

by:countrymeister
ID: 22762941
I am just trying to compile the proc , I am not running this.
All I have is a an If / else condition  which has two sets of Create statements with the same Temp table name
So you are saying excute the create within some SQL string
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 2000 total points
ID: 22763009
Yes. I realize you are just compiling at this point. What happens when you run the following?
Select * from  #ActivityInfo1
Does the table exist? If so, drop it. If not, then your stored procedure will not compile  because it sees two create stmts for the same object. So trick it.. :0
declare @SqlStmt as varchar(8000);
set @SqlStmt = "CREATE ........"
EXECUTE (@SqlStmt)
0
 
LVL 1

Author Closing Comment

by:countrymeister
ID: 31508057
Got it, thanks
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

688 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