asked on

Create a table in a stored procedure with

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

Brian Crowe

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

Scott Pletcher

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.

ram2098

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

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer