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

  • DECLARE @sql nvarchar(4000)

    SET @sql = 'SELECT s.ItemID,s.ItemName,s.ItemCost'

    SELECT @sql = @sql + ',s.' + c.[name]

    FROM sysobjects o

    INNER JOIN syscolumns c

    ON c.id = o.id

    AND c.[name] NOT IN ('ItemID','ItemName','ItemCost')

    WHERE o.xtype = 'U' and o.name = 'Store'

    ORDER BY c.colid

    SET @sql = @sql + ' FROM Store s LEFT JOIN City c ON c.ItemID = s.ItemID'

    EXEC sp_executesql @sql

    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