Using variable as a column heading 1

[yehong]

Hi, I have this dyanamic query in which I want to show the value of a variable @val1 as the column heading.

Declare @val1 varchar(20)
Select @val1='City'
Select id,
case
when table1.field1 = @val1 then table1.field2
end as 'xxxxxxxx' (this should be the value of @val1)
from table1

The result should look like:
ID City
123 Los Angeles

Next time when the value of @val1 is 'Department', then out put should look like
ID Department
123 IT


When I do case...end as @val1, its not working. The val1 can be a city, can be a departement etc.

 

[RiverGuy]

You could use dynamic sql, but it's a much better idea to handle this on the front-end reporting tool or application.

 

[yehong]

Any other opinion please? I need to write this sql in a store proc and I can't use any front-end reporting tool or application.
-thanks.

 

[bborissov]

As RiverGuy already said you should uyse so caller Dynamic SQL:

CREATE PROCEDURE MyTest(
        @ColumnName varchar(500)
        )
AS
BEGIN
    DECLARE @sql varchar(8000)
    SET @sql = 'SELECT Id, '+ @ColumnName+
               '       FROM MyTable'
    EXEC (@sql)
END

Keep in mind that Dynamic Queries are performance killers.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP