Something like this should work:
SELECT
CASE obj.Objective_Status
WHEN 1 THEN 'Live'
WHEN 2 THEN 'Draft'
WHEN 3 THEN 'Completed'
ELSE 'Obsolete'
END AS 'Objective_Status',
COUNT(*) 'Percentage',
CASE obj.Objective_Status
WHEN 1 THEN 2
WHEN 2 THEN 1
WHEN 3 THEN 3
END as OrderOfObjectives
FROM
Objectives as obj with (nolock)
WHERE
Exists
(
SELECT 1
FROM @empObjectives As EO
CROSS APPLY dbo.Split(ObjectivesIDsLive, ',') As ID
WHERE ID.value = obj.Id
)
Or
Exists
(
SELECT 1
FROM @empObjectives As EO
CROSS APPLY dbo.Split(ObjectivesIDsDraft, ',') As ID
WHERE ID.value = obj.Id
)
Or
Exists
(
SELECT 1
FROM @empObjectives As EO
CROSS APPLY dbo.Split(ObjectivesIDsCompleted, ',') As ID
WHERE ID.value = obj.Id
)
GROUP BY
obj.Objective_Status
ORDER BY
OrderOfObjectives
;
You might need to change the "value" column name on the three
WHERE ID.value = obj.Id
lines to match the column name returned from your
Split
function.
It would be better to change your
@empObjectives
table variable so that the IDs weren't combined as a comma-separated list.