We would like to use a parameter for the "Order By" of this query in the Visual Studio DataSet Designer:
SELECT Students.ID, Students.Surname, Students.Forename, Students.DateOfBirth, Parents.MotherName,
Parents.FatherName, Parents.AddressLine1, Parents.AddressLine2, Parents.City,
Parents.State + ' ' AS State, Parents.Zip,
CASE
WHEN len(ltrim(rtrim(Parents.PrimaryPhone))) = '10' THEN
'(' + SUBSTRING(Parents.PrimaryPhone, 1, 3) + ')' + ' ' +
SUBSTRING(Parents.PrimaryPhone, 4, 3) + '-' +
SUBSTRING(Parents.PrimaryPhone, 7, 4)
WHEN len(ltrim(rtrim(Parents.PrimaryPhone))) = '7' THEN
SUBSTRING(Parents.PrimaryPhone, 1, 3) + '-' +
SUBSTRING(Parents.PrimaryPhone, 4, 4)
WHEN len(ltrim(rtrim(Parents.PrimaryPhone))) = '' THEN
' '
END AS PrimaryPhone,
CASE
WHEN len(ltrim(rtrim(Parents.SecondaryPhone))) = '10' THEN
'(' + SUBSTRING(Parents.SecondaryPhone, 1, 3) + ')' + ' ' +
SUBSTRING(Parents.SecondaryPhone, 4, 3) + '-' +
SUBSTRING(Parents.SecondaryPhone, 7, 4)
WHEN len(ltrim(rtrim(Parents.SecondaryPhone))) = '7' THEN
SUBSTRING(Parents.SecondaryPhone, 1, 3) + '-' +
SUBSTRING(Parents.SecondaryPhone, 4, 4)
WHEN len(ltrim(rtrim(Parents.SecondaryPhone))) = '' THEN
' '
END AS SecondaryPhone,
Parents.HomeEmail
FROM Parents INNER JOIN
Students ON Parents.ID = Students.ParentID
WHERE (Students.Forename LIKE '%' + @SearchValue + '%') OR
(Students.Surname LIKE '%' + @SearchValue + '%') OR
(@SearchValue = 'ALL')
ORDER BY @OrderByColumn
This error is displayed:
Variables are only allowed when ordering by an expression referencing
a column name.
We need help doing what the message is describing?
Try the following query.....
ORDER BY
CASE WHEN @OrderByColumn='Students.ID' THEN Students.ID END,
CASE WHEN @OrderByColumn='Students.Surname' THEN Students.Surname END