How can I get the dynamic OrderBy to work properly? Below is a simple sample:
DECLARE @test-2 SYSNAME
SET @test-2 = 'ColOne'
SELECT
'A' AS ColOne,
'D' AS ColTwo
UNION
SELECT
'C' AS ColOne,
'B' AS ColTwo
ORDER BY
--ColOne /*This Works*/
/*This does not*/
CASE @test-2
WHEN 'ColOne' THEN ColOne
WHEN 'ColTwo' THEN ColTwo
END
Here's an override (this works but cannot use for another reason):
DECLARE @test-2 SYSNAME
SET @test-2 = 'ColOne'
SELECT * FROM
(
SELECT
'A' AS ColOne,
'D' AS ColTwo
UNION
SELECT
'C' AS ColOne,
'B' AS ColTwo
) DynamicTable
ORDER BY
--ColOne /*This Works*/
/*This does not*/
CASE @test-2
WHEN 'ColOne' THEN DynamicTable.ColOne
WHEN 'ColTwo' THEN DynamicTable.ColTwo
END
Now - see my previous post about errors in using UDF's inside subqueries. That's why I'm trying to avoid the "Outer Query".
Thanks - B