Post reply

In SQL Server 2005 or later, column aliases in the ORDER BY clause cannot be prefixed by the table alias

  • June 17, 2010 at 12:02 am

    #220191

    Hi,

    I got the below warning from the SQL Server 2008 Upgrade adviser for a stored procedure.

    In SQL Server 2005 or later, column aliases in the ORDER BY clause cannot be prefixed by the table alias

    Exception: If the prefixed column alias that is specified in the ORDER BY clause is a valid column name in the specified table, the query executes without error

    We have procedure as below:

    We have procedure as below:

    CREATE PROCEDURE [dbo].[ABC_SP]

    @Debugint = NULL

    AS

    SET NOCOUNT ON

    SELECTP1studID AS [studID],

    P1ID AS [ControlID],

    P1Step AS [Step],

    styType AS [StepType],

    stySubType AS [StepSubType],

    P1ChangeNumber AS [ChangeNumber],

    lpo.nexpnum AS [ExpiredNumber],

    P1Status AS [Status],

    P1Suppress AS [Suppress],

    P1_stcID AS [stcID],

    P1.timestamp AS [timestamp]

    FROMdbo.abc P1 INNER JOIN

    dbo.abcStepType ON (P1_styID = styID) LEFT OUTER JOIN

    dbo.Mytbl lpo ON (P1studID = (-1 * lpo.studID) AND

    P1ChangeNumber = lpo.ncrtnum)

    WHEREP1Status = 1

    andstyType <> 'BackOff'

    andstySubType <> 'BackOff_Execute'

    ORDER BYP1.timestamp ASC

    RETURN 0

    In above procedure the prefixed column alias that is specified in the ORDER BY clause is P1.

    and the Column name is update_timestamp in the specified table dbo.abc. So it's comes under the exception or not?

    Do we need to modify the procedure?

    This procedure is running fine in current SQL 2005 (which was earlier upgraded from SQL 2000) and I belive it should run fine in SQL 2008 too. Because Upgrade adviser says,

    In SQL Server 2005 or later, column aliases in the ORDER BY clause cannot be prefixed by the table alias.

    So it's working in SQL 2005 means it also works in SQL 2008 right?

    Please clarify me

    Thanks

  • June 17, 2010 at 12:16 am

    #1181375

    The query is fine, because you're referring to a column that's actually in the table. The warning appears because you're aliasing the column in the select clause as [timestamp], and then you're ordering by something with that name. You either order by columns you're returning, or you order by columns in tables - it's worried you might be confusing the two. You're not, so it's fine.

    If you want to avoid the warning, you should remove the 'as [timestamp]' in your select clause.

    Rob

    Rob Farley
    LobsterPot Solutions & Adelaide SQL Server User Group
    Company: http://www.lobsterpot.com.au
    Blog: http://blogs.lobsterpot.com.au

Viewing 2 posts - 1 through 1 (of 1 total)

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