Post reply

Dynamic caption in a query

  • January 16, 2007 at 8:03 am

    #115663

    Hello friends, someone know if is possible to build a query with dynamic captions from a field of table?

    example

    instead of

    example select a.code 'Product' from Table1 a

    i want something like

    select   a.code      b.caption  from Table1 a, Table2 b

    where a.id = b.id

    ------------------------------------------------------------------------

    where 'Product' is in b.caption

    ------------------------------------------------------------------------

    Product

    23445

    2334

    4455

    666

     

  • January 16, 2007 at 9:56 am

    #683535

    Hi, you'll need to post the DDL (data definition language) of your table and be a bit more specific about your problem...

    Are you looking to name the result column with your caption, or, are you trying to return something like the following:

    code       caption

    -----------------

    1234       OneCaption

    5678       AnotherCaption

    If you can expand on your problem this would be extremely helpful!


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • January 16, 2007 at 10:05 am

    #683536

    Sorry my bad english well, i want to the first option, i am looking to name the result column with the caption which is defined into field from a Table, thanks for try to compile my DLL

  • January 16, 2007 at 10:25 am

    #683543

    I'm a bit confused as to why you would want a dynamic caption, if you could explain more about the problem, there may be a better way around it...

    To solve the problem as it stands, you'd need to use dynamic SQL ( ), something like:

    DECLARE @sSQL NVARCHAR(100)

    DECLARE @idSelect INT

    SET @idSelect = 1

    SELECT @sSql = 'SELECT code AS ' + caption + ' FROM table1 WHERE id = ' + CAST(@idSelect AS VARCHAR)

    FROM table2

    WHERE id = @idSelect

    EXEC sp_ExecuteSql @sSql

    If you use the PRINT @sSql statement before the EXEC statement, you can see the string that was built and executed.

    Using dynamic SQL is not great for performance, so if you can explain further, there might be a better way to approach this.


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • January 16, 2007 at 11:11 am

    #683561

    thanks friend , i need to maintenance a report which is built with a store procedure, if a caption changes on the web interface, (those captions are stored into a system table), i need to modify the sp to change the caption in the query, so i think to do some dynamic procedure to do that.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply