I work with an application that, in many places, uses stored procedures to return data to the application, kind of like a view, but these procedures take in parameters (which isn't possible in a SQL Server view).
I believe this was done due to it being more efficient for the given queries to have values passed deep into the inner queries, than it was to use a view with a WHERE
clause.
In these cases, none of the other features you get by using a stored procedure are required e.g. they are all only select statements... no transactions, it's a single result set, no error handling, no calling to other procedures, etc.
For this specific use case - which is basically a "parameterized view" - are there efficiencies to be gained by using an inline user-defined function that returns a table data type instead of stored procedures for this?
The use case I am dealing with involves very small result sets. A simple example would be where a web app calls a proc with, say, a member id parameter, the proc contains a query with a bunch of joins and subqueries etc. (the parameter is used deep within these) and then send back a list of, say, subscriptions, owned by that user.
Is one method generally better than the other?