SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Create a table dynamically
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

waxdart23
Starting Member

United Kingdom
33 Posts

Posted - 05/25/2006 :  07:27:58  Show Profile  Visit waxdart23's Homepage  Reply with Quote
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

India
394 Posts

Posted - 05/25/2006 :  07:45:12  Show Profile  Reply with Quote
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

United Kingdom
33 Posts

Posted - 05/25/2006 :  08:07:28  Show Profile  Visit waxdart23's Homepage  Reply with Quote
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

India
394 Posts

Posted - 05/25/2006 :  08:15:43  Show Profile  Reply with Quote
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

United Kingdom
33 Posts

Posted - 05/25/2006 :  08:22:49  Show Profile  Visit waxdart23's Homepage  Reply with Quote
Great, thats exactly what I needed. Many thanks for the quick response.

Thanks
P
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000