getting values for dynamically generated columns??
-
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
-
Best way to go is to prefix all "dynamically created columns" with for example "dc_"
as "dc_ItemHeight" or "dc_ItemColor".
This way there is no confusion of which are "fixed" columns and dynamic columns.
N 56°04'39.16"
E 12°55'05.25" -
CREATE proc SP_AFSearch
as
declare @Column as varchar(300),
@strquery as varchar(8000)
Declare CRS_AddColumn CURSOR FOR select syscolumns.name Name
from syscolumns
inner 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
set @strquery = 'select Store.ItemID [Item Code], Store.ItemName [Item Name], Store.ItemCost [Item Cost]'
WHILE @@FETCH_STATUS = 0
BEGIN
set @srtquery = @srtquery + ', Store.' + @Column
FETCH NEXT FROM CRS_AddColumn INTO @Column
end
CLOSE CRS_AddColumn
DEALLOCATE CRS_AddColumn
set @srtquery = @srtquery + ' from Store left join City on Store.ItemID = City.ItemID'
print @strquery
exec (@strquery)
GO
N 56°04'39.16"
E 12°55'05.25" -
However, this is a SP I would use. I don't like cursors that much.
CREATE PROCEDURE usp_AFSearch
AS
DECLARE @SQL VARCHAR(8000)
SELECT @SQL = ISNULL(@SQL + ', ', ' ') + z.TableName + '.' + z.ColumnName
FROM (
SELECT so.name TableName,
sc.name ColumnName
FROM SYSCOLUMNS sc
INNER JOIN SYSOBJECTS so ON so.id = sc.id
WHERE so.xtype = 'U'
AND so.name = 'STORE'
AND sc.name NOT IN ('ITEMID', 'ITEMNAME', 'ITEMCOST')
ORDER BY sc.colid
) z
SELECT @SQL = 'SELECT Store.ItemID [Item Code], Store.ItemName [Item Name], Store.ItemCost [Item Cost]' + @sql + ' FROM Store LEFT JOIN City ON Store.ItemID = City.ItemID'
PRINT @SQL
EXEC (@SQL)
GO
N 56°04'39.16"
E 12°55'05.25" -
HI..peter
that i want exactly
Thank you very much
Regards,
Papillon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply