User-defined functions

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Call a scalar function created within a rolled-back teaching transaction. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

BEGIN TRANSACTION;
EXEC(N'CREATE FUNCTION dbo.DDI_LessonAddTax(@Price decimal(10,2))
RETURNS decimal(10,2) AS BEGIN RETURN @Price * 1.10; END');
SELECT ProductID, dbo.DDI_LessonAddTax(ListPrice) AS PriceWithTax
FROM dbo.Products WHERE ProductID BETWEEN 1 AND 2 ORDER BY ProductID;
ROLLBACK TRANSACTION;

Read it step by step

1. The scalar function receives a decimal price and returns a decimal value.
2. The query invokes it for two products; it does not modify ListPrice.
3. ROLLBACK removes the practice function. Inline expressions or inline table-valued functions can be preferable for performance.

Check the result

The declared decimal(10,2) return type determines the displayed two-decimal result.

Practice

Create a rolled-back function that doubles a product price and call it for ProductID 1.

BEGIN TRANSACTION;
EXEC(N'CREATE FUNCTION dbo.DDI_LessonDouble(@Price decimal(10,2))
RETURNS decimal(10,2) AS BEGIN RETURN @Price * 2; END');
SELECT ProductID, dbo.DDI_LessonDouble(ListPrice) AS DoubledPrice
FROM dbo.Products WHERE ProductID = 1;
ROLLBACK TRANSACTION;