Variable In the ORDER BY clause in TSQL ??? HELP ! 1

[KBCobra]

Here is the current SP

SELECT dbo.PolicyHolderDataT.*, MuellerFormT.*, MuellerGlobalData.dbo.JoinedCustomerTable.*, dbo.StateT.*, dbo.UnderwriterAgents.* FROM MuellerGlobalData.dbo.InternetAuthorizedCompanies
INNER JOIN MuellerGlobalData.dbo.JoinedCustomerTable ON MuellerGlobalData.dbo.InternetAuthorizedCompanies.nJoinedCustomerTable_id = MuellerGlobalData.dbo.JoinedCustomerTable.ID
INNER JOIN dbo.CustomerT ON MuellerGlobalData.dbo.JoinedCustomerTable.CustomerNumber = dbo.CustomerT.CustomerNumber INNER JOIN dbo.PolicyHolderDataT INNER JOIN dbo.StateT ON dbo.PolicyHolderDataT.SurveyStateID = dbo.StateT.StateID ON dbo.CustomerT.ID = dbo.PolicyHolderDataT.CustomerID
LEFT OUTER JOIN dbo.MuellerFormT ON dbo.PolicyHolderDataT.ControlIDNumber = dbo.MuellerFormT.InsuredID LEFT OUTER JOIN dbo.UnderwriterAgents ON dbo.PolicyHolderDataT.AgencyCode = dbo.UnderwriterAgents.cAgentCode
WHERE ( 1=1 AND MuellerGlobalData.dbo.InternetAuthorizedCompanies.nInternetUsers_id = @nInternetUsers_id)
and CustomerT.CustomerNumber = ISNULL(@nCustomerID,CustomerT.CustomerNumber)
and PolicyHolderDataT.PolicyNumber LIKE ISNULL(@cPolicyNumberVar,PolicyHolderDataT.PolicyNumber)
and ControlIDNumber = ISNULL(@nControlIDNumber,ControlIDNumber)
and StatusIDLookup = ISNULL(@cStatusIDLookup,StatusIDLookup)
and AgencyCode = ISNULL(@cAgencyCode,AgencyCode)
and (UnderwriterAgents.cUnderwriterCode =ISNULL(@cUnderwriterCode,UnderwriterAgents.cUnderwriterCode) OR UnderwriterAgents.cUnderwriterTechCode = ISNULL(@cUnderwriterCode,UnderwriterAgents.cUnderwriterTechCode)
and PolicyHolderDataT.DateCompleted BETWEEN ISNULL(@dCompletedFrom,PolicyHolderDataT.DateCompleted) and ISNULL(@dCompletedTo,PolicyHolderDataT.DateCompleted)
and PolicyHolderDataT.DateReceived BETWEEN ISNULL(@dReceivedFrom,PolicyHolderDataT.DateReceived) and ISNULL(@dReceivedTo,PolicyHolderDataT.DateReceived)
and PolicyHolderDataT.EffectiveDate BETWEEN ISNULL(@dEffectiveFrom,PolicyHolderDataT.EffectiveDate) and ISNULL(@dEffectiveTo,PolicyHolderDataT.EffectiveDate)
and PolicyHolderDataT.PolicyHolderName2 LIKE ISNULL(@cPolicyHolderName2Var,PolicyHolderDataT.PolicyHolderName2)
ORDER BY ISNULL(@cSortBy,PolicyHolderDataT.ControlIDNumber) DESC



Its blowing up on the order by clause , does anyone know of a good way to pass in the order by clause and have it recognized and work ? I am trying to avoid string concatination with an EXEC statement due to compiling concerns.

Thanks,
Pat

 

[swampBoogie]

e.g.

order by case
 when @sortBy = 'C1' then C1
 when @sortBy = 'C2' then C2
 end

The columns C1 and C2 must be comparable. You may need to have multiple case expressions if the columns you are sorting by have different types.

 

[KBCobra]

ORDER BY
case
when @cSortBy = 'PolicyHolderDataT.PolicyNumber' then PolicyHolderDataT.PolicyNumber
when @cSortBy = 'PolicyHolderDataT.PolicyHolderName2' then PolicyHolderDataT.PolicyHolderName2
when @cSortBy = '(PolicyHolderDataT.CoverageAIn / MuellerFormT.nReplacementCost)' then (PolicyHolderDataT.CoverageAIn / MuellerFormT.nReplacementCost)
when @cSortBy = '((MuellerFormT.nMajorRisks * 3) + MuellerFormT.nMinorRisks)' then ((MuellerFormT.nMajorRisks * 3) + MuellerFormT.nMinorRisks)
when @cSortBy = 'PolicyHolderDataT.StatusIDLookup' then PolicyHolderDataT.StatusIDLookup
when @cSortBy = 'PolicyHolderDataT.DateReceived' then PolicyHolderDataT.DateReceived
when @cSortBy = 'PolicyHolderDataT.DateCompleted' then PolicyHolderDataT.DateCompleted
when @cSortBy = 'MuellerFormT.dWebReviewed' then MuellerFormT.dWebReviewed
when @cSortBy = 'PolicyHolderDataT.EffectiveDate' then PolicyHolderDataT.EffectiveDate
when @cSortBy = 'MuellerFormT.cReviewedBy' then MuellerFormT.cReviewedBy
when @cSortBy = 'PolicyHolderDataT.DateAssigned' then PolicyHolderDataT.DateAssigned
when @cSortBy = 'PolicyHolderDataT.AssignedToInspector' then PolicyHolderDataT.AssignedToInspector
when @cSortBy = 'PolicyHolderDataT.AgencyCode' then PolicyHolderDataT.AgencyCode
when @cSortBy = 'UnderwriterAgents.cUnderwritercode' then UnderwriterAgents.cUnderwritercode
when @cSortBy = 'UnderwriterAgents.cUnderwriterTechcode' then UnderwriterAgents.cUnderwriterTechcode
when @cSortBy = 'MuellerGlobalData.dbo.JoinedCustomerTable.CustomerNumber' then MuellerGlobalData.dbo.JoinedCustomerTable.CustomerNumber
when @cSortBy = 'PolicyHolderDataT.ControlIDNumber' then PolicyHolderDataT.ControlIDNumber

end



Still errors out saying incorrect syntax near ORDER

 

[KBCobra]

LOLLOLLOL

This works

ORDER BY ISNULL(@cSortBy, PolicyHolderDataT.PolicyNumber)

I was missing an end &quot"

Sometimes the simplest things get ya