Functions

The Function class in Weasel.SqlServer.Functions manages T-SQL user-defined functions (scalar and table-valued) with delta detection.

Creating a Function from SQL

The simplest approach is to use Function.ForSql(), which parses the function name from the SQL body:

var fn = Function.ForSql(@"
CREATE FUNCTION dbo.CalculateDiscount(@Price DECIMAL(10,2), @Rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
RETURN @Price * @Rate;
END;
");

^{snippet source | anchor}

Constructor-Based Creation

You can also construct a Function directly with an identifier and body:

var identifier = DbObjectName.Parse(SqlServerProvider.Instance, "dbo.GetUserCount");

var fn = new Function(identifier, @"
CREATE FUNCTION dbo.GetUserCount()
RETURNS INT
AS
BEGIN
RETURN (SELECT COUNT(*) FROM dbo.users);
END;
");

^{snippet source | anchor}

Custom Drop Statements

When a function has overloads or requires special cleanup, provide custom drop statements:

var fn = new Function(identifier, body, new[]
{
    "DROP FUNCTION IF EXISTS dbo.GetUserCount;"
});

^{snippet source | anchor}

Delta Detection

Function deltas are detected by querying sys.sql_modules and comparing definitions:

await using var conn = new SqlConnection(connectionString);
await conn.OpenAsync();

var delta = await fn.FindDeltaAsync(conn);
// delta.Difference: None, Create, or Update

^{snippet source | anchor}

Marking for Removal

To generate a drop statement for a function that should be removed:

var removed = Function.ForRemoval("dbo.ObsoleteFunction");

^{snippet source | anchor}