Table Valued UDF vs Stored Proc for passing values to a SELECT statement 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?
The answer partially depends on what you do with the output of the object. Both stored procedures and table-valued functions support inserting into table, but using an inline table-valued function can be significantly more efficient.

Below are a few silly object definitions for the demo. Both of them generate a result set of about 6.4 million rows with a single column.

CREATE PROCEDURE dbo.DUMMY_PROCEDURE (@i BIGINT)
AS
BEGIN
    SELECT CAST(NULL AS BIGINT) COL
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    WHERE @i &lt;&gt; 0;
END;

GO

CREATE FUNCTION dbo.DUMMY_FUNCTION (@i BIGINT)
returns table
as
return (
    SELECT CAST(NULL AS BIGINT) COL
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    WHERE @i &lt;&gt; 0
);

GO


Next I'll insert the results of the stored procedure into a temp table. In general, this isn't a good thing to do for result sets that aren't tiny. The results are written to a separate internal structure before being inserted into the table. SQL Server uses a cardinality estimate of 1 row for the insert which can also cause performance issues.

DROP TABLE IF EXISTS #TARGET_TABLE;
CREATE TABLE #TARGET_TABLE (ID BIGINT);

-- CPU time = 11514 ms,  elapsed time = 10748 ms
INSERT INTO #TARGET_TABLE WITH (TABLOCK)
EXEC dbo.DUMMY_PROCEDURE 1;


The query took about 11 seconds. I uploaded the actual plan here if you want to take a look. Here's a screenshot of the worst part:



Things are much better with the function. The function definition is inlined and I'm eligible for parallel insert in SQL Server 2016. The insert takes about half a second.

DROP TABLE IF EXISTS #TARGET_TABLE;
CREATE TABLE #TARGET_TABLE (ID BIGINT);

-- CPU time = 1562 ms,  elapsed time = 520 ms.
INSERT INTO #TARGET_TABLE WITH (TABLOCK)
SELECT COL
FROM dbo.DUMMY_FUNCTION (1);


The query plan is healthier and much more typical:



There might be other internals differences in addition to that. I couldn't tell you.