As Mark say „Please provide a reproducible example.”

>> also like i said if i run this same query as a standalone outside the SP it run fine ..

Single query is not the answer because in stored procedure you have loop with multiple records

regards,

Karol Bieniaszewski