dynamic number of columns

[garfunct]

when using sorred procedure to create a temporary table is it possable to base the number of columns in that table on another variable?

[MAK]

example?

[Rawhide]

Like MAK, I'm not sure I quite understand the question, but you can create your "Create Table" statement dynamically and then execute the statement using either Exec() or sp_executesql.

[garfunct]

for example
--asume a loop for each account_number
declare @tbl
(
account_number int,
term int,
month1 money,
--and here is where the number of months columns is what would very for each account_number.
some accounts would only be 6 months and some 60
)

select * from @tbl

hope this is clear enough...?

[Rawhide]

Declare @SQL nvarchar(4000),
@Months int,
@counter int

Set @Months = 12
Set @counter = 1
Set @SQL = 'Create Table dbo.MyTable (account_number int not null, term int not null'

While @counter <= @Months
Begin
Set @SQL = @SQL + ', month' + Cast(@counter as nvarchar) + ' money null'

Set @counter = @counter + 1
End

Set @SQL = @SQL + ')'

Exec sp_executesql(@SQL)



select * from @tbl

[garfunct]

Perfect, I will give this a try.
FYI.
Thank you this is my first time using this forum and am very impressed with the speed of response!!

Hope I can help some one else in the same way.