Solved

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

Posted on 2008-10-20
4
272 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

776 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