Is there ever a reason to re-write an inline table-valued function as a stored procedure?

J. Mini 0
Viewed 46

I've found a very strange practice in my workplace. I'm surrounded by experts, particularly .NET experts. But for reasons unknown to me, many of their stored procedures would work perfectly fine as inline table-valued functions. They wouldn't even need refactoring. You'd just swap CREATE PROCEDURE for CREATE FUNCTION and check that it RETURNS TABLE.

This gives me my question: Given a perfectly fine inline table-valued function, is there any reason at all to make it a stored procedure instead?

sql-server-2012 user-defined-functions tsql stored-procedures sql-server
2 Answers

Given a perfectly fine inline table-valued function, is there any reason at all to make it a stored procedure instead?

Probably just familiar with stored procedures, but there are some reasons you might prefer a proc over an inline TVF.

  1. Execution plan depends only on the input parameters, and so is more stable and simpler to test. The flexibility of TVF to be composed into larger queries is very powerful, but can cause performance issues.

  2. Stored procedures support arbitrary TSQL statements, so you can add logging or other side-effects, print output for debugging, materialization of intermediate results to temporary tables, etc, without changing client code.

David Browne - Microsoft0

A Table Valued Function cannot be run by itself, it must be contained in another query. In that respect, and in many others, it is basically a parameterized view, or a saved query.

You cannot insert, update or delete using a function. While you can actually modify through the function (similar to updatable views), the function itself cannot contain any DML, or any other side-affecting commands. It is also more difficult or impossible to specify various query hints.

Stored procedures on the other hand contain the full script of what needs to be done for a particular task. They can contain almost any command you can do from an ad-hoc batch. They therefore represent a complete Unit of Work.

They are not as composable as functions though. It is very difficult to pass data from one to the other, and they are best used a single entry-point from a client app.

Due to the afore-mentioned composable and updatable abilities of functions, it might actually be worthwhile to use both together.

For example, you could have a function that returns Products that were not sold:

CREATE OR ALTER FUNCTION Product.NotSoldProducts (@From datetime, @To datetime)
RETURNS TABLE
AS RETURN
SELECT p.*
FROM Product.Product p
WHERE NOT EXISTS (SELECT 1
    FROM Sales.Sale s
    WHERE s.ProductId = p.Id 
);

You could use this in a stored procedure to get the data, but careful use of subqueries (ie no joins) also allows this to be updatable.

CREATE OR ALTER PROC Promotion.PromoteNotSoldProducts
  @From datetime,
  @To datetime,
  @UntilDate datetime,
  @Percent int
AS

SET NOCOUNT, XACT_ABORT ON;

BEGIN TRAN;

UPDATE sp
SET PromotionPercent = @Percent,
    PromotedUntil = @UntilDate
FROM Product.NotSoldProducts (@From, @To) sp;

INSERT Product.AuditLog (AuditType, Message)
SELECT p.Id, 'Promotion', CONCAT('Promoted for ', @Percent, '% until ', @UntilDate)
FROM Product.NotSoldProducts (@From, @To) sp;

COMMIT;

Note that the modifications, as well as the SET options and the transaction could not be done inside the function.

Charlieface0
Related