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.
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 intdeclare @colnum intdeclare @colname varchar(3)select @session_cnt = (select count(*) from [dbo].lt_rs_project_sessions where inv_no = @inv_no)select @colnum = 1select @colname = 'S'while @session_cnt > 0begin 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 ThanksP |
|
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? |
|
|
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.ThanksP |
|
|
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 > 0begin 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'endexec ('alter table #tmp_custdet add ' + @sqlstring ) |
|
|
waxdart23
Starting Member
33 Posts |
Posted - 2006-05-25 : 08:22:49
|
Great, thats exactly what I needed. Many thanks for the quick response.ThanksP |
|
|
|
|
|