Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
Stored Procedures: Parameters, Error Handling, Ownership Chaining and Safe Dynamic SQL
The right stored procedure skeleton is SET NOCOUNT ON, SET XACT_ABORT ON, ROLLBACK inside TRY/CATCH and a parameterless THROW; otherwise a CHECK error can commit half an order. Reading output parameters and return codes from Dapper, ownership chaining that gives the application user only EXECUTE, safe dynamic SQL with an allowlist, QUOTENAME and sp_executesql, and repairing the chain dynamic SQL breaks with module signing, with real output.
Stored Procedures with Dapper: Output, Return Value and the Auto-Detection Trap of 2.1.11
SQLite has no stored procedures, so we move to SQL Server in docker: CommandType.StoredProcedure, output and return values with DynamicParameters, how SET NOCOUNT affects Execute, single-word commands mistaken for procedures since 2.1.11, and the function vs procedure difference in PostgreSQL.