Solved

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

Posted on 2008-10-20
4
275 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 500 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

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