Stored Procedures — Design, Parameterization, Security

Use a stored procedure when:
  → Complex multi-step SQL: multiple UPDATEs, conditionals, loops in a transaction
  → Performance-critical batch operations: bulk updates/inserts that benefit from
    compiled execution plans
  → Legacy system integration: the database is shared by multiple applications —
    the procedure defines the contract
  → Security boundary: grant EXECUTE on the procedure without granting table access

Use EF Core / Dapper instead when:
  → Simple CRUD: EF Core generates efficient SQL and you gain type safety
  → Query flexibility: EF Core with LINQ is more composable than static procedures
  → ORM-managed migrations: EF Core migrations track schema changes; procedures need
    separate migration scripts
  → Testability: in-memory testing is easier without procedures

Rule: stored procedures are a tool for specific cases — not the default approach for all SQL in .NET.

-- Stored procedure for the prescription approval workflow
-- Uses a transaction, validates state, and returns a result code

CREATE OR ALTER PROCEDURE dbo.ApprovePrescription
    @PrescriptionId    UNIQUEIDENTIFIER,
    @ApproverId        UNIQUEIDENTIFIER,
    @InrValue          DECIMAL(4,2),
    @InrCheckedAt      DATETIME2,
    @ResultCode        NVARCHAR(20) OUTPUT,
    @ResultMessage     NVARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Validate the prescription exists and is in Draft status
        DECLARE @CurrentStatus NVARCHAR(20);
        SELECT @CurrentStatus = Status
        FROM   dbo.Prescriptions WITH (UPDLOCK, ROWLOCK)
        WHERE  Id = @PrescriptionId;

        IF @CurrentStatus IS NULL
        BEGIN
            SET @ResultCode    = 'NOT_FOUND';
            SET @ResultMessage = 'Prescription not found.';
            ROLLBACK TRANSACTION;
            RETURN;
        END

        IF @CurrentStatus != 'Draft'
        BEGIN
            SET @ResultCode    = 'INVALID_STATUS';
            SET @ResultMessage = 'Prescription must be in Draft status to approve. Current: ' + @CurrentStatus;
            ROLLBACK TRANSACTION;
            RETURN;
        END

        -- Validate INR is within therapeutic range for Warfarin
        DECLARE @MedicationName NVARCHAR(200);
        SELECT @MedicationName = MedicationName
        FROM   dbo.Prescriptions
        WHERE  Id = @PrescriptionId;

        IF @MedicationName = 'Warfarin' AND (@InrValue < 2.0 OR @InrValue > 3.0)
        BEGIN
            SET @ResultCode    = 'INR_OUT_OF_RANGE';
            SET @ResultMessage = 'Warfarin INR of ' + CAST(@InrValue AS NVARCHAR(10)) +
                                 ' is outside the therapeutic range (2.0–3.0). Cannot approve.';
            ROLLBACK TRANSACTION;
            RETURN;
        END

        -- Perform the approval
        UPDATE dbo.Prescriptions
        SET    Status       = 'Approved',
               ApprovedBy  = @ApproverId,
               ApprovedAt  = SYSUTCDATETIME(),
               InrValue    = @InrValue,
               InrCheckedAt = @InrCheckedAt
        WHERE  Id = @PrescriptionId;

        -- Audit log
        INSERT INTO dbo.PrescriptionAuditLog
            (PrescriptionId, Action, PerformedBy, PerformedAt, Detail)
        VALUES
            (@PrescriptionId, 'Approved', @ApproverId, SYSUTCDATETIME(),
             'INR: ' + CAST(@InrValue AS NVARCHAR(10)));

        COMMIT TRANSACTION;

        SET @ResultCode    = 'SUCCESS';
        SET @ResultMessage = 'Prescription approved successfully.';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

        SET @ResultCode    = 'ERROR';
        SET @ResultMessage = 'Unexpected error: ' + ERROR_MESSAGE();

        -- Re-throw for logging in the application:
        THROW;
    END CATCH
END;

// Option 1: Dapper — best for procedures that return data or output parameters
// NuGet: Dapper

public sealed class PrescriptionApprovalRepository
{
    private readonly string _connectionString;

    public async Task<ApprovalResult> ApproveAsync(
        Guid     prescriptionId,
        Guid     approverId,
        decimal  inrValue,
        DateTime inrCheckedAt,
        CancellationToken ct)
    {
        await using var conn = new SqlConnection(_connectionString);

        var parameters = new DynamicParameters();
        parameters.Add("@PrescriptionId", prescriptionId);
        parameters.Add("@ApproverId",     approverId);
        parameters.Add("@InrValue",       inrValue);
        parameters.Add("@InrCheckedAt",   inrCheckedAt);
        parameters.Add("@ResultCode",     dbType: DbType.String, size: 20,
                       direction: ParameterDirection.Output);
        parameters.Add("@ResultMessage",  dbType: DbType.String, size: 500,
                       direction: ParameterDirection.Output);

        await conn.ExecuteAsync(
            "dbo.ApprovePrescription",
            parameters,
            commandType: CommandType.StoredProcedure,
            commandTimeout: 30);

        var resultCode    = parameters.Get<string>("@ResultCode");
        var resultMessage = parameters.Get<string>("@ResultMessage");

        return resultCode == "SUCCESS"
            ? ApprovalResult.Success()
            : ApprovalResult.Failure(resultCode, resultMessage);
    }
}

public sealed record ApprovalResult(bool IsSuccess, string? ErrorCode, string? Message)
{
    public static ApprovalResult Success()                          =>
        new(true,  null,      null);
    public static ApprovalResult Failure(string code, string msg)  =>
        new(false, code,      msg);
}

// Option 2: EF Core FromSqlRaw / ExecuteSqlRawAsync — for procedures in EF Core context

// Calling a procedure that returns a result set:
var prescriptions = await _context.Prescriptions
    .FromSqlRaw("EXEC dbo.GetActivePrescriptionsByWard @WardId = {0}", wardId)
    .ToListAsync(ct);

// Calling a procedure for side effects (no result set):
await _context.Database.ExecuteSqlRawAsync(
    "EXEC dbo.SuspendExpiredPrescriptions @CutoffDate = {0}",
    DateTime.UtcNow.AddDays(-30));

// Option 3: SqlCommand for full control (complex output params, multiple result sets):
await using var conn = new SqlConnection(_connectionString);
await conn.OpenAsync(ct);

await using var cmd = new SqlCommand("dbo.ApprovePrescription", conn)
{
    CommandType    = CommandType.StoredProcedure,
    CommandTimeout = 30
};

cmd.Parameters.AddWithValue("@PrescriptionId", prescriptionId);
cmd.Parameters.AddWithValue("@ApproverId",     approverId);
cmd.Parameters.AddWithValue("@InrValue",       inrValue);
cmd.Parameters.AddWithValue("@InrCheckedAt",   inrCheckedAt);

var resultCodeParam   = cmd.Parameters.Add("@ResultCode",    SqlDbType.NVarChar, 20);
var resultMessageParam = cmd.Parameters.Add("@ResultMessage", SqlDbType.NVarChar, 500);
resultCodeParam.Direction    = ParameterDirection.Output;
resultMessageParam.Direction = ParameterDirection.Output;

await cmd.ExecuteNonQueryAsync(ct);

var code    = (string)resultCodeParam.Value;
var message = (string)resultMessageParam.Value;

-- Create a database role for the application:
CREATE ROLE AppRole;

-- Grant execute on specific procedures only — NOT table access:
GRANT EXECUTE ON dbo.ApprovePrescription    TO AppRole;
GRANT EXECUTE ON dbo.ListActivePrescriptions TO AppRole;
GRANT EXECUTE ON dbo.GetPatientSummary       TO AppRole;

-- Explicitly DENY direct table access:
DENY SELECT ON dbo.Prescriptions TO AppRole;

-- Application user is a member of AppRole only:
CREATE USER ClinicalAppUser FOR LOGIN ClinicalAppLogin;
ALTER ROLE AppRole ADD MEMBER ClinicalAppUser;

-- Now the application can only call approved procedures —
-- it cannot execute arbitrary SELECT/UPDATE/DELETE on tables.
-- SQL injection via raw queries is blocked at the permission level.

-- Stored procedures excel at batch operations that would be N queries from .NET

CREATE OR ALTER PROCEDURE dbo.ExpirePrescriptionsOlderThan
    @CutoffDate  DATETIME2,
    @ExpiredCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.Prescriptions
    SET    Status    = 'Expired',
           ExpiredAt = SYSUTCDATETIME()
    WHERE  Status      = 'Approved'
      AND  ApprovedAt  < @CutoffDate;

    SET @ExpiredCount = @@ROWCOUNT;

    -- Insert audit entries for all expired prescriptions in one INSERT:
    INSERT INTO dbo.PrescriptionAuditLog
        (PrescriptionId, Action, PerformedBy, PerformedAt, Detail)
    SELECT
        Id,
        'Expired',
        '00000000-0000-0000-0000-000000000000',  -- system action
        SYSUTCDATETIME(),
        'Auto-expired: approved before ' + CONVERT(NVARCHAR(30), @CutoffDate, 126)
    FROM dbo.Prescriptions
    WHERE Status = 'Expired'
      AND ExpiredAt >= DATEADD(SECOND, -5, SYSUTCDATETIME()); -- just-expired rows

    RETURN;
END;

-- Called from a Timer-triggered Azure Function or background service:
// var parameters = new DynamicParameters();
// parameters.Add("@CutoffDate",  DateTime.UtcNow.AddDays(-30));
// parameters.Add("@ExpiredCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
// await conn.ExecuteAsync("dbo.ExpirePrescriptionsOlderThan", parameters, commandType: CommandType.StoredProcedure);
// var count = parameters.Get<int>("@ExpiredCount");