In my experience, is good to have some way to use SPs instead Table Valued Functions because in a Function you can't use a temporal table to reduce the universe, and this is something very important when you work with a lot of records.

By other side, I see a deep problem of concurrency. If I have several users calling the store procedure, the last win.

I mean, if I have 3 users calling the store procedure to select customers, and this customers should be in the resulting table.

If first need customers with customer name like '%jho%'

the second user need customers with customer name like '%Mik%'

the third user need customers with customer name like '%Mij%'

and three users ask at same time the resulset, the 3 users will get the same results in the table, the data matching with last user where.

Is there some way to avoid it?