Solved

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

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Connect MySql database to wordpress site 3 54
Create A query for disabled users 5 30
SyBase Query Syntax Date Time conversion 4 27
Trigger usage 2 59
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now