SQL Server stored procedures for complex data operations

Complex data work rarely fits into a single neat query. Anyone who has built a billing engine for a Perth-based energy retailer, a reporting pipeline for a Brisbane council, or a reconciliation job for one of the big four banks in Sydney will tell you that the interesting business logic sits in the gaps between tables. Filters branch, totals need to align to the cent, and audit columns have to be written alongside the actual change. Stored procedures in SQL Server are still one of the most honest ways to express that logic, even in a stack that otherwise leans on Entity Framework, Dapper, or a slick front-end framework like Angular.

This piece walks through how I approach SQL Server stored procedures for complex data operations in production systems. The goal is not to evangelise them as a silver bullet, but to share a working pattern: how to design parameters, manage transactions, tune performance, write tests, and keep the whole thing secure enough to pass a review with an Australian privacy officer looking over your shoulder.

Why stored procedures still earn a seat at the table

ORMs like Entity Framework Core are excellent for the 80 percent of queries that are simple reads, single inserts, and straightforward updates. The other 20 percent is where things get interesting. Multi-step calculations, branching logic that depends on data already in the database, and bulk operations across millions of rows are the kind of work where moving the logic to the database engine pays off. Round trips drop, locks are held for shorter windows, and the database can use the statistics it has on its own data instead of guessing what the application thinks is there.

There is also a pragmatic reason. Plenty of teams in Melbourne, Adelaide, and beyond still run hybrid systems where a .NET API talks to a SQL Server instance, and that instance is shared with reporting tools, legacy applications, and BI dashboards. A stored procedure becomes a stable contract: the application sends a few parameters, the database does the heavy lifting, and everyone else who needs the same result can call into the same routine. That contract is easier to reason about than three slightly different LINQ queries written by three different developers over the last eighteen months.

None of this means you should reach for a procedure on every problem. I still write plain SQL inside repositories when the logic is straightforward. But for anything that involves conditional aggregation, iterative reconciliation, or producing a denormalised result for downstream consumers, a well-written procedure tends to be both faster to develop and easier to maintain than the equivalent C# code that pulls everything into application memory.

Designing parameters that survive contact with real data

The first mistake I see in junior-written procedures is treating parameters as a free pass to ignore typing. Every parameter gets a data type, every nullable column gets a default of NULL, and every date gets passed as a proper DATETIME2 rather than a string. The reason is straightforward: SQL Server uses the parameter's data type to build an execution plan, and a poorly typed parameter is the most common cause of parameter sniffing headaches later on.

Australian data adds its own quirks. Phone numbers arrive in mixed formats, postcodes are four digits and not always numeric, and business names contain characters that you would not believe. The procedure signature has to reflect that. I usually build input parameters like this.

Conventions worth standardising across the team

• @StartDate DATETIME2 and @EndDate DATETIME2 for any windowed work, with explicit UTC conversion at the boundary so a server in Sydney and a client in Perth agree on the same instant.
• @CustomerIds NVARCHAR(MAX) parsed with STRING_SPLIT when callers want to pass a list, paired with a TRY_CAST to a table variable so junk input fails fast.
• @IncludeArchived BIT for soft-delete patterns, defaulting to 0 so the safe path is also the obvious one.
• @RequestId UNIQUEIDENTIFIER for correlation, generated by the calling application and logged in an audit table.

A useful pattern is to wrap the public procedure in a thin parameter-validation shell, then call an internal procedure that does the real work. The shell handles nulls, trims strings, and rejects obvious garbage, while the worker assumes clean input. This split makes both pieces far easier to test in isolation, and it is a habit that pays back the first time someone tries to call the procedure from a legacy VBA macro.

Transactions, error handling, and the cost of half-finished work

The single most important sentence in any procedure that writes data is BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH. Without it, a failure halfway through a multi-step operation can leave a customer with a debited account and no corresponding invoice, and that is the kind of bug that gets you on a call with someone at the ATO or with a compliance officer. Wrap the body in SET XACT_ABORT ON for belt and braces, and decide explicitly whether you want a runtime error to roll back the whole transaction.

Inside the catch block, capture the error number, message, severity, and the procedure name into local variables, then re-throw with THROW so the calling application can react. Logging the same detail to an ErrorLog table is cheap insurance, especially when the production database is on a server you cannot RDP into from your apartment in Carlton. I have lost count of the number of times a stored ErrorLog entry has saved me from guessing what the user did three days earlier.

Transactions deserve their own paragraph. Keep them short. If a procedure is doing ten different things, the locks it takes will block other workers, and a long transaction is the leading cause of deadlocks in busy Australian retail systems. Read the data you need into temp tables early, then perform the writes in a tight transaction at the end. The pattern reads like this: gather, validate, write, log, commit. Anything that talks to the network, an external service, or a file should happen outside the transaction, or in a separate one entirely.

Performance patterns for complex multi-step work

A few patterns come up over and over in the procedures I write. They are not exotic, but they are easy to forget when you are trying to ship a feature by Friday and the existing one is limping along. None of them are silver bullets, but together they make a procedure that holds up under load.

Patterns that pay off in heavy workloads

• Set-based operations first, row-by-row only when there is no alternative. A WHILE loop in T-SQL is a code smell until you have proven that a set-based approach is actually slower, which is rarer than people think.
• Temp tables for staging, table variables for tiny lookups. Temp tables have statistics and can be indexed, which makes them ideal for intermediate result sets larger than a few hundred rows. Table variables are fine for a lookup of branch codes or a handful of ProductType enums.
• Explicit OPTION (RECOMPILE) on the hot path of a parameter-sensitive procedure, used sparingly. It trades plan caching for plan freshness, which can be the right call when one parameter shifts the row count by three orders of magnitude.
• SET NOCOUNT ON at the top of every procedure. It removes the row-count messages that would otherwise clutter the network and slow down high-volume operations.
• Indexing the source tables, not just the procedure. A procedure that filters on CreatedAt and BranchCode will always struggle if the underlying table is only indexed on Id.

I also keep a habit of running the procedure through STATISTICS IO and STATISTICS TIME before shipping. A small console app or even sqlcmd can pump representative parameters through the procedure, and the resulting logical reads are a more honest indicator of performance than the milliseconds reported by the application.

Testing stored procedures without losing your weekends

Procedural SQL has a reputation for being hard to test, which is mostly true if you only have SSMS and a copy of production. Set up a dedicated test database, seed it with deterministic data, and use tSQLt as the framework. Each test becomes a class that creates the tables it needs, calls the procedure, and asserts on the resulting state. The framework is free, runs in SQL Server Management Studio, and integrates into Azure DevOps pipelines without drama.

A useful rule of thumb is to write three tests per procedure: a happy path with typical input, a boundary case with nulls and empty strings, and a failure case where the inputs should produce a controlled error. The failure case is the one that often gets skipped, and it is the one that catches bugs in the CATCH block six months after release when something changes in an upstream table.

If you are in a smaller team, the testing discipline does not need to be heavy. Even a handful of IF ... PRINT 'OK' scripts saved in source control is better than relying on the developer's memory. The point is that the next person to touch the procedure should be able to run the same checks you did, and a clear test file in the same repo as the .NET solution is the simplest way to make that happen. Several Melbourne-based teams I have worked with store their database projects as .sqlproj files alongside the API, which keeps schema, procedures, and tests in one place.

Security, compliance, and keeping auditors comfortable

Stored procedures live close to the data, which means they are a natural place to enforce security. Grant the application login EXECUTE on the procedure and nothing else, so the application cannot accidentally read a salary table by typing a slightly different table name into a debug query. Use a separate schema for procedures, such as app, and keep the underlying tables in a dbo schema that the application login cannot see. This pattern is sometimes called the stored procedure gateway, and it is still the cleanest way to keep a SQL Server surface area small.

In Australia, the Privacy Act and the Australian Privacy Principles put obligations on how personal information is handled, and that includes the database layer. A procedure that aggregates health, financial, or tax data should log who ran it, when, and with which parameters. A simple Audit table written to inside the same transaction gives you a tamper-evident trail, and the same table can feed the access reports that an internal review will eventually ask for. If the system touches data that flows to the ATO or to a state revenue office, the same audit mechanism will be useful during a review.

Encryption is the last layer. Sensitive columns should use Always Encrypted or, at minimum, column-level encryption with a key stored in Azure Key Vault or a hardware security module. Backups need to be encrypted as well, particularly when they leave the on-premises server and end up in an Australian data centre or in a long-term archive. A procedure that does the right thing in code is still a liability if the backup sitting in a cold storage account can be read by anyone with a guessed password.

If you are working on your next data-heavy feature, pick one procedure in your current codebase and refactor it using the patterns above. Add parameters with proper types, wrap the work in a transaction with a real CATCH block, and write three tSQLt tests. Once you see how much safer the change feels, you will not want to go back to the old way of sprinkling INSERT statements across a C# service. Subscribe to the blog for more deep dives into SQL Server, .NET, and the realities of shipping software from a developer's perspective, and check out the open-source Jekyll theme if you want a clean place to publish your own technical writing.