Getting values for dynamically generated columns??
-
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
-
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. -
Actually, this is the 4th identical post
If you're not happy with an answer, please say so within the same thread instead of posting the same thing 4 times...See...
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=302520
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=302527
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=302524
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=302523
... or, maybe you've just had a bit too much "Dew" and hit the mouse button too many times
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply