ORDER BY with an aliased column name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ORDER BY with an aliased column name

Author

Topic

sillybus
Starting Member

13 Posts

Posted - 2001-05-22 : 14:33:15

This one has had me stumped for a few days so I've decided to ask for help from the forum.

I've been using aliased column names in my SPROCs for quite some time and even passed in those aliased names to do a dynamic order by such as this:

CREATE PROCEDURE usp_allUsers (
@orderBy varChar(100) = NULL
)
AS

SET NOCOUNT ON

declare @strSQL varChar(500)
create table #tempReport
(
[First Name] varchar(100),
[Middle Name] varchar(100),
[Last Name] varchar(100),
[Title] varchar(100),
[Address 1] varchar(255),
[Address 2] varchar(255),
[City] varchar(50),
[State] varchar(2),
[Zip] varchar(20),
[Email] varchar(255),
[Phone] varchar(50),
[Fax] varchar(50),
[Website] varchar(255)
)

insert #tempReport
SELECT DISTINCT
rcd_prefix as [Prefix],
rcd_firstName as [First Name],
rcd_middleName as [Middle Name],
rcd_lastName as [Last Name],
rcd_title as [Title],
rcd_address1 as [Address 1],
rcd_address2 as [Address 2],
rcd_city as [City],
rcd_state as [State],
rcd_zipcode as [Zip],
rcd_email as [Email],
rcd_phone as [Phone],
rcd_fax as [Fax],
rcd_website as [Website]
FROM regPayRegistrantCoreData

set @strSQL = 'select'
+ ' *'
+ ' from #tempReport'

if len(@orderBY) > 0
begin
set @orderBy = '[' + @orderBy + ']'
set @strSQL = @strSQL
+ ' ORDER BY '
+ @orderBy
end

execute(@strSQL)

drop table #tempReport

Now the problem I am having is when I am in a situation where I can't use a stored procedure. Using ADO with ASP to do an order by such as this:

(All the aliasing has been removed to keep it short)

select * from users
order by [last name]

This will give me an error "Incorrect syntax near the keyword 'ORDER'"

Any ideas are appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources