Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Create a table in a stored procedure with

Posted on 2005-03-04
4
Medium Priority
?
1,332 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
  • 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 70

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 70

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

572 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