?
Solved

Create a table in a stored procedure with

Posted on 2005-03-04
4
Medium Priority
?
1,323 Views
Last Modified: 2011-09-20
Hi,
i´m trying to create a table in a stored procedure and set the columns in a while loop like this but the table is never created.
Suggestions?
----------------------
declare
@DateFrom smalldatetime,
@DateTo smalldatetime,
@tbl varchar(1000)

set @DateFrom = '2005-01-01'
set @DateTo = dateadd(mm,1,'2005-03-01')

set @tbl = 'create table #tblTmp (ID Int IDENTITY(1,1)'
 while @DateFrom <= @DateTo
  begin
  set @tbl = @tbl + ' , Col' + cast(datepart(mm,@DateFrom) as varchar(2)) + ' smallint NULL'
  set @DateFrom = dateadd(mm,1,@DateFrom)
  if @DateFrom = @DateTo
   break
  else
   continue
  end
set @tbl = @tbl + ')'
execute(@tbl)
drop table #tblTmp
-------------------------------------
Best / jens
0
Comment
Question by:jenmat
[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
4 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13460361
declare
@DateFrom smalldatetime,
@DateTo smalldatetime,
@tbl nvarchar(1000)

set @DateFrom = '2005-01-01'
set @DateTo = dateadd(mm,1,'2005-03-01')

set @tbl = 'create table tblTmp (ID Int IDENTITY(1,1)'
 while @DateFrom <= @DateTo
  begin
  set @tbl = @tbl + ', Col' + cast(datepart(mm,@DateFrom) as varchar(2)) + ' smallint NULL'
  set @DateFrom = dateadd(mm,1,@DateFrom)
  if @DateFrom = @DateTo
   break
  else
   continue
  end
set @tbl = @tbl + ')'
execute sp_executesql @tbl
drop table tblTmp
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 13460366
A temp table created in an EXEC() will be automatically destroyed when the EXEC() ends, so even if the EXEC works, you won't see the temp table when it ends.
0
 
LVL 11

Expert Comment

by:ram2098
ID: 13460385
The problem is.....

you are trying to create a temp table using exec...so it is actually creating a table, but it is not accessible outside this statement, since it is a temp table.

If you want to create a table like this,

either you have to create a Global temp table like ##tblTemp

(or) a permenant table like tblTemp

HTH..
Ram
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 13460425
If you could create the temp table in the main procedure, you could use the EXEC() code to alter the table and add as many additional columns as you needed, and the resulting table, with the new columns, would  be available to the original procedure.  For example:

CREATE TABLE #tblTmp (ID Int IDENTITY(1,1))'

set @tbl = 'alter table #tblTmp add'
 while @DateFrom <= @DateTo
  begin
  set @tbl = @tbl + ' , Col' + cast(datepart(mm,@DateFrom) as varchar(2)) + ' smallint NULL'
  set @DateFrom = dateadd(mm,1,@DateFrom)
  if @DateFrom = @DateTo
   break
  else
   continue
  end
-- remove comma before first column added
set @tbl = REPLACE(@tbl, '#tblTmp add ,', '#tblTmp add ')
execute(@tbl)

...temp table processing...

drop table #tblTmp
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

765 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