Dynamic caption in a query

Question

Dynamic caption in a query

Hector Angello Silva Carmona

SSCrazy

Points: 2720
More actions
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

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

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

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]

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 2

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 3

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]

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 4

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.