Drop the SET and do the assignment as part of the select

declare @sequence_number int

select @sequence_number =

case

when workflow_activity_type_identifier = 197 then '1'

else

case when workflow_activity_type_identifier = 203 then '1'

else(select count(*)

from workflow_activity

where workflow_activity_type_identifier in (200,201)

and workflow_activity_identifier = @workflow_activity_identifier

and workflow_identifier=@workflow_identifier)

end

end

from workflow_activity

Just be warned, if there's more than 1 row in the workflow_activity table, it's not easy to say which row will be the one evaluated for the variable.

What, exactly, are you trying to do with this statement?