Stored Procedures

The StoredProcedure class in Weasel.SqlServer.Procedures manages T-SQL stored procedures as schema objects with full delta detection support.

Defining a Stored Procedure

Provide the complete T-SQL body when constructing the stored procedure:

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

var proc = new StoredProcedure(identifier, @"
CREATE PROCEDURE dbo.usp_get_active_users
@MinAge INT = 18
AS
BEGIN
SET NOCOUNT ON;
SELECT Id, Name, Email
FROM dbo.users
WHERE Active = 1 AND Age >= @MinAge;
END;
");

^{snippet source | anchor}

Generating DDL

var migrator = new SqlServerMigrator();
var writer = new StringWriter();

// CREATE PROCEDURE
proc.WriteCreateStatement(migrator, writer);

// CREATE OR ALTER PROCEDURE (for updates)
proc.WriteCreateOrAlterStatement(migrator, writer);

// DROP PROCEDURE IF EXISTS
proc.WriteDropStatement(migrator, writer);

^{snippet source | anchor}

Delta Detection

The StoredProcedureDelta compares the expected procedure body against what exists in the database by querying sys.sql_modules:

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

var delta = await proc.FindDeltaAsync(conn);
if (delta.Difference == SchemaPatchDifference.Create)
{
    // Procedure does not exist yet
}
else if (delta.Difference == SchemaPatchDifference.Update)
{
    // Procedure body has changed
}

^{snippet source | anchor}

Fetching Existing Definitions

var existing = await proc.FetchExistingAsync(conn);
if (existing != null)
{
    // existing contains the current procedure body from the database
}

^{snippet source | anchor}