August 18, 2006 at 2:42 am
Hi,,,,
We have table (say table name “Store”) in which some columns are generated dynamically and data in dynamically generated columns filled by users. Now I want to make one search reports in that all the columns should display. I don’t have any problem for fixed columns but I am getting trouble in dynamically generated columns.
Say for example :
I have fixed columns in Store are ItemID,ItemName,ItemCost suppose now ItemColor and ItemHeight are generated dynamically then how I display that in report???
I can see the dynamically generated columns by
select syscolumns.name Name from syscolumns left join sysobjects
on sysobjects.ID = syscolumns.ID
where sysobjects.xtype = 'U' and sysobjects.name = ‘Store’ and syscolumns.name not in (‘ItemID’,’ItemName’,’ItemCost’)
OUTPUT: Name
ItemColor
ItemHeight
Now I made one stored procedure like
CREATE proc SP_AFSearch
as
declare @Column as varchar(100)
declare @strquery as varchar(1000)
Declare CRS_AddColumn CURSOR FOR select syscolumns.name Name from syscolumns left join sysobjects
on sysobjects.ID = syscolumns.ID
where sysobjects.xtype = 'U' and sysobjects.name = ‘Store’ and syscolumns.name not in (‘ItemID’,’ItemName’,’ItemCost’)
OPEN CRS_AddColumn
FETCH NEXT FROM CRS_AddColumn INTO @Column
WHILE @@FETCH_STATUS = 0
BEGIN
set @strquery = '(select Store.ItemID as [Item Code],Store.ItemName as [Item Name],Store.ItemCost as [Item Cost], and here I want that columns ItemColor and ItemHeight means @Column
from Store left join City
on Store.ItemID = City.ItemID)’
print @strquery
exec (@strquery)
FETCH NEXT FROM CRS_AddColumn INTO @Column
END
CLOSE CRS_AddColumn
DEALLOCATE CRS_AddColumn
GO
But I am unable to solve it please help me out
T.I.A
Regards,
Papillon
August 18, 2006 at 7:18 am
Duplicate post, see
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=302527
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply