Set Order by Using a CASE Statement 1

[LeonelSanchezJr]

I have the following code in which I want to set the "ORDER BY" depending on what the value of @cDocType is, but I get an error stating that "IS NULL", "HAWB", "PO", "INLND_BL" and "POD" are invalid column names: s-)

Select
Hawb.cHawbNum
, Hawb.dShipDate
, Hawb.cOrigin
, Hawb.cDestin
, HawbDetail.cCustcode
, HawbDetail.dDueDate
, #TmpHawbsNoDoc.cDocType
From Hawb (nolock)
Join HawbDetail (nolock) on Hawb.cHawbNum = HawbDetail.cHawbNum
Join #TmpHawbsNoDoc On Hawb.cHawbNum = #TmpHawbsNoDoc.cHawbNum
Order by
Case @cDocType
When Is Null Then 'cCustCode, cOrigin, dShipDate'
When "HAWB" Then 'cCustCode, cOrigin, dShipDate'
When "PO" Then 'cCustCode, cOrigin, dShipDate'
When "INLND_BL" Then 'cCustCode, cOrigin, dShipDate'
When "POD" Then 'cCustCode, cDestin, dDueDate'
End

 

[tlbroadbent]

You need to create the SQL statement first and then execute it with sp_executesql or Execute.

Declare @sql varchar(1024)
Set @sql=
'Select
Hawb.cHawbNum
, Hawb.dShipDate
, Hawb.cOrigin
, Hawb.cDestin
, HawbDetail.cCustcode
, HawbDetail.dDueDate
, #TmpHawbsNoDoc.cDocType
From Hawb (nolock)
Join HawbDetail (nolock) on Hawb.cHawbNum = HawbDetail.cHawbNum
Join #TmpHawbsNoDoc On Hawb.cHawbNum = #TmpHawbsNoDoc.cHawbNum
Order by ' +
Case @cDocType
When Is Null Then 'cCustCode, cOrigin, dShipDate'
When "HAWB" Then 'cCustCode, cOrigin, dShipDate'
When "PO" Then 'cCustCode, cOrigin, dShipDate'
When "INLND_BL" Then 'cCustCode, cOrigin, dShipDate'
When "POD" Then 'cCustCode, cDestin, dDueDate'
End

exec sp_executesql @sql Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.