I suspect that one of your records in the User_Master table has the created_by set to the same value as the user_id.

This sample works fine for me, but uncommenting the 1st value in the insert and commenting out the second causes your error.

CREATE TABLE #User_Master (

user_id INT

, created_by INT)

;

INSERT #User_Master

VALUES

--(1195, 1195)

(1195, 1196)

, (1234, 1195)

, (5678, 1234)

;

with child_user(user_id, created_by)

as (

Selectuser_id, created_by

from #User_Master where user_id= 1195

union all

select ro.user_id, ro.created_by

from #User_Master ro

join child_user cv on cv.user_id = ro.Created_by

)

select

user_id, created_by from child_user CV

DROP TABLE #User_Master