Hi Guys,

I have the following stored procedure which I use to pull hotel
information from the database. The following procedure does just what I
need but I can't get the Case statement to work with the OrderBy
clause.

When I try I get the following error:

Error 104: Order by items must appear in the select list if the
statement contains a union operator.


This is confusing because the order by item does appear in the select
list.

Is there anyway to make this work withour resorting to dynamic sql?

Cheers, Pete


CREATE PROCEDURE [dbo].[SelectHotelsByList]

@Target_Language nvarchar(50),
@Default_Language nvarchar(50),
@Hotels_Key nvarchar(255),
@OrderBy nvarchar(255)

AS

BEGIN


SELECT

[Hotels_Name]

FROM [dbo].[vHotels]


WHERE [Hotels_LanguageCode] = @Default_language

AND [Hotels_Key] NOT IN

(
SELECT [Hotels_Key]

FROM [dbo].[vHotels]

WHERE [Hotels_LanguageCode] = @Target_language

)


AND [Hotels_key] = @Hotels_Key


UNION ALL


SELECT

[Hotels_Name]

FROM [dbo].[vHotels]


WHERE [Hotels_LanguageCode] = @Target_language


AND [Hotels_key] = @Hotels_Key


ORDER BY

CASE WHEN @OrderBy = 'HotelsNameAsc' THEN [Hotels_Name]
WHEN @OrderBy = 'HotelsNameDesc' THEN [Hotels_Name]
ELSE [Hotels_Name]
END


END
GO

Your ORDER BY clause, as shown above, results in ORDER BY [Hotels_Name]
in all cases.

I'm sure you meant to include DESC at end of second case clause :)

Try declaring a variable @SQL varchar(2000) at beginning of script and
then
SELECT @SQL='SELECT [Hotels_Name] ' +
'FROM [dbo].[vHotels] ' +
'WHERE [Hotels_LanguageCode] = ' + @Default_language +
' AND [Hotels_Key] NOT IN ' +
' (SELECT [Hotels_Key] ' +
' FROM [dbo].[vHotels] ' +
' WHERE [Hotels_LanguageCode] = ' +
@Target_language + ') ' +
' AND [Hotels_key] = ' + @Hotels_Key +
' UNION ALL ' +
' SELECT [Hotels_Name] ' +
' FROM [dbo].[vHotels] ' +
' WHERE [Hotels_LanguageCode] = ' + @Target_language +
' AND [Hotels_key] = ' + @Hotels_Key

IF @OrderBy = 'HotelsNameDesc'
SELECT @SQL=@SQL + ' ORDER BY [Hotels_Name] DESC'
ELSE
SELECT @SQL=@SQL + 'ORDER BY [Hotels_Name]'

EXEC (@SQL)

the brackets are vital around @SQL above

HTH
Regards
Alan