Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Create a table dynamically

Author  Topic 

waxdart23
Starting Member

33 Posts

Posted - 2006-05-25 : 07:27:58
I am trying to add rows to a temp table in a stored procedure based on a row count from another table. I am using the code below which is obviously not allowed. Is there another way of doing this?


declare @session_cnt int
declare @colnum int
declare @colname varchar(3)

select @session_cnt = (select count(*) from [dbo].lt_rs_project_sessions where inv_no = @inv_no)
select @colnum = 1
select @colname = 'S'

while @session_cnt > 0
begin
select @colname = @colname + convert(varchar(2), @colnum)
alter table #tmp_custdet add @colname char(1) null
select @session_cnt = @session_cnt - 1
select @colnum = @colnum + 1
select @colname = 'S'
end


Thanks
P

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-05-25 : 07:45:12
What you said is not matching with the Code. i.e you said you want to add that many rows to the table as the count but as per ur code you are trying to add that many columns to the table.
What exactly is ur requirement?
Go to Top of Page

waxdart23
Starting Member

33 Posts

Posted - 2006-05-25 : 08:07:28
Yes, sorry, I meant columns. I wish to add columns to a temp table based on a row count from another table.

Thanks
P
Go to Top of Page

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-05-25 : 08:15:43
You can build a dynamic string as
declare @sqlstring varhcar(100)
set @sqlstring = ''
while @session_cnt > 0
begin
select @colname = @colname + convert(varchar(2), @colnum)
set @sqlstring = @sqlstring + @colname + 'char(1) null'
select @session_cnt = @session_cnt - 1
select @colnum = @colnum + 1
select @colname = 'S'
end

exec ('alter table #tmp_custdet add ' + @sqlstring )
Go to Top of Page

waxdart23
Starting Member

33 Posts

Posted - 2006-05-25 : 08:22:49
Great, thats exactly what I needed. Many thanks for the quick response.

Thanks
P
Go to Top of Page
   

- Advertisement -