April 8, 2009 at 6:46 am
Hi,
What I want to know is how do I create dynamic columns for a temp table in a stored procedure;?
Here is the start of the procedure..
CREATE PROCEDURE Sp_GetAvergaeSales
(
@StartDate Datetime,
@EndDate DateTime,
@NumOfWeeks int
)
AS
Declare
@i int,
@ColumnName varchar(10)
create table #weeks(XSeries varchar(12), Turnover money)
set @i = 0
while @i <= @NumOfWeeks
begin
set @ColumnName = 'Turnover' + cast(@i as varchar(2))
--alter table #weeks Add @ColumnName money -- This doesnt work.... 🙂
set @i = @i + 1
end
Q) How do I add the dynamic number of columns in this procedure, Any ideas...
April 8, 2009 at 7:32 am
Try this,
CREATE PROCEDURE Sp_GetAvergaeSales
(
@StartDate Datetime,
@EndDate DateTime,
@NumOfWeeks int
)
AS
Declare
@i int,
@ColumnName varchar(10),
@AlterTable varchar(100),
@AddColumn varchar(100),
@tsqlalter varchar(100)
create table #weeks(XSeries varchar(12), Turnover money)
set @i = 0
while @i <= @NumOfWeeks
begin
set @ColumnName = 'Turnover' + cast(@i as varchar(2))
select @alterTable='alter table #weeks'
select @Addcolumn = @columnname
SELECT @tsqlalter = @AlterTable+@AddColumn+'money'
--alter table #weeks Add @ColumnName money -- This doesnt work.... 🙂
set @i = @i + 1
end
April 8, 2009 at 7:59 am
Opps I am sorry u have to add this in the code too
EXEC(@tsqlalter)
--after the
SELECT @tsqlalter = @AlterTable+@AddColumn+'money'
so it should be
SELECT @tsqlalter = @AlterTable+@AddColumn+'money'
EXEC(@tsqlalter)
April 8, 2009 at 8:03 am
In addition to the "EXEC" always have TRY..CATCH block to avoid any un-expected errors during the operations.
April 8, 2009 at 9:28 am
Mayank's code is missing one more thing, and that is the word "ADD" in front of the column name... but you can easily correct that.
April 8, 2009 at 1:04 pm
thanks 🙂
April 8, 2009 at 1:17 pm
nice
April 24, 2009 at 7:45 am
Thanks its works fine
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply