Loading ...
Sorry, an error occurred while loading the content.

Can a variable be used to set a column alias?

Expand Messages
  • gonforward
    I would like to customize a column header by specifying an alias in a Select statement using a variable instead of literal. My Select statements error every
    Message 1 of 2 , Nov 30, 2003
    • 0 Attachment
      I would like to customize a column header by specifying an alias in
      a "Select" statement using a variable instead of literal. My Select
      statements error every time I try to use a variable but work fine
      using a literal...

      For example, using a literal everything works:

      Select Builder_Name as [My Builders] from Tbl_builders

      My Builders
      ------------
      Centex Homes
      Fox and Jacobs

      Using a declared variable to the same value it errors:

      Declare @mytitle char (11)
      set @mytitle = 'My Builders'
      Select Builder_Name as @mytitle from Tbl_builders

      Server: Msg 170, Level 15, State 1, Line 3
      Line 3: Incorrect syntax near '@mytitle'.

      I have tried many variations of the statement above and searched for
      an internet example in vain. I would be grateful for a solution
      using a variable!!

      Thanks,

      David (gonforward@...)
    • Jeff Burgess
      The only thing I can think of is a dynamically-created SQL statement. Be aware that this approach can introduce security vulnerabilities. I m not sure about
      Message 2 of 2 , Dec 1, 2003
      • 0 Attachment
        The only thing I can think of is a dynamically-created SQL statement. Be aware that this approach can introduce security vulnerabilities. I'm not sure about other platforms, but this is how you would do it in SQL Server:
         
        DECLARE @MyTitle varchar(20)
        SET @MyTitle = '[My Builders]'
        EXECUTE 'SELECT Builder_Name AS ' + @MyTitle + ' FROM Tbl_builders'
        -----Original Message-----
        From: gonforward [mailto:gonforward@...]
        Sent: Sunday, November 30, 2003 10:42 PM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: [SQLQueriesNoCode] Can a variable be used to set a column alias?

        I would like to customize a column header by specifying an alias in
        a "Select" statement using a variable instead of literal.  My Select
        statements error every time I try to use a variable but work fine
        using a literal...

        For example, using a literal everything works:

          Select Builder_Name as [My Builders] from Tbl_builders

          My Builders
          ------------
          Centex Homes
          Fox and Jacobs

        Using a declared variable to the same value it errors:

          Declare @mytitle char (11)
          set @mytitle = 'My Builders'
          Select Builder_Name as @mytitle from Tbl_builders

          Server: Msg 170, Level 15, State 1, Line 3
          Line 3: Incorrect syntax near '@mytitle'.

        I have tried many variations of the statement above and searched for
        an internet example in vain.  I would be grateful for a solution
        using a variable!!

        Thanks,

        David (gonforward@...)





        To unsubscribe from this group, send an email to:
        SQLQueriesNoCode-unsubscribe@yahoogroups.com



        Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
      Your message has been successfully submitted and would be delivered to recipients shortly.