Once Again: Stored Procedure Vs. Tv-udf

I know, this was discussed several times (most discussions are some years back...). But - looking through these discussions - I get the feeling, that there is a broadly spread comm

Solution 1:

I think you are comparing apples and oranges and I at least have never seen any discussions about this. There are discussions about if you should use UDFs at all and there are discussions about if you should use stored procedures or ad hoc SQL.

An inline UDF is something you can use in a query and a stored procedure is something you can execute and most of your bullet points is a consequence of that difference.

An inline UDF is more like a view than a stored procedure. A parameterized view that can be used in queries and can sometimes be used to speed things up.

Best performance (I did a lot of comparisons!)

I would very much like to see a scenario where an inline UDF and stored procedure does the same thing and have different performance.

And - last but not least - as the UDF never writes anything, it is much lighter in point of locking

If the stored procedure never writes anything there is no difference in locking.

So: Why are so many people using SPs to collect data?

Don't know about people but for me it is all about the discussion on stored procedure vs ad hoc sql. I prefer stored procedures other prefer ad hoc. If you want to use user defined functions instead of procedures you end up in the ad hoc sql camp.