MS SQL 2012 - Need to split records from 4 different fields then query those
Welcome to SQL, you can replace your sample code with this:

DECLARE @ParentBaseId nvarchar(100) = '16-0833'

SELECT * FROM WORK_ORDER
WHERE BASE_ID IN 
(
SELECT USER_1 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId 
UNION
SELECT USER_2 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId 
UNION
SELECT USER_3 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId 
UNION
SELECT USER_4 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId 
)


It's not the fanciest or even best way, but is the easiest to start learning with.

By the way, your code won't work if @UDF_1 is null, you need to assign '' to @ALL_UDF before working with it, or add other checks.