Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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

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
countrymeister
Asked:
countrymeister
  • 2
  • 2
1 Solution
 
HoggZillaCommented:
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
 
countrymeisterAuthor Commented:
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
 
HoggZillaCommented:
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
 
countrymeisterAuthor Commented:
Got it, thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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