SELECT *
FROM MyTable
WHERE
Col1 LIKE
CASE Col2
WHEN '1' THEN 'X'
WHEN '2' then 'Y'
ELSE 'Z'
END

CREATE PROCEDURE GetEmployees (
@ColName varchar(100)
)
AS
SELECT Col1, Col2
FROM Employees
ORDER BY
CASE
WHEN @ColName='X' THEN CONVERT(char(50), Col1) + CONVERT(char(50), Col2)
WHEN @ColName='Y' THEN CONVERT(varchar(50), Col1)
WHEN @ColName='Z' THEN Z
END