Stored Procedures, Parameters, Result Sets, Recompilation, and Ownership Chaining

Learning outcomes

ServiceHub has reached the point where several applications, scheduled jobs, and support scripts all need the same “find work orders” behavior. Today each caller embeds slightly different SQL. One application returns a different column order, another forgets the status rule, and a third has direct table permissions that no longer match the security model. The problem is no longer just query syntax. It is an API contract inside the database.

A stored procedure is a named server-side module containing Transact-SQL statements. It can accept input parameters, expose output parameters, return one or more result sets, return an integer status code, participate in transactions, and execute under SQL Server permission rules. The useful mental model is not “saved query.” A procedure is deployable code with a caller contract, a security boundary, compilation state, dependencies, failure semantics, and operational telemetry.

1. Build the API contract before the body

The most maintainable procedures start with an explicit contract: parameter names and SQL types, nullability/default semantics, result-set columns and types, error behavior, transaction ownership, permissions, and versioning expectations. Parameter types should match the indexed columns they filter; otherwise conversion can damage cardinality estimates or SARGability before the procedure body gets interesting.

SELECT SERVERPROPERTY('ProductVersion') AS product_version,       SERVERPROPERTY('ProductUpdateLevel') AS update_level,       SERVERPROPERTY('Edition') AS edition,       SERVERPROPERTY('EngineEdition') AS engine_edition;GOSELECT name,compatibility_level,is_query_store_onFROM sys.databasesWHERE name IN (N'ServiceHubLab',N'ServiceHubProgrammabilityLab');GOSELECT name,value_in_useFROM sys.configurationsWHERE name IN (N'clr enabled',N'clr strict security',N'nested triggers');GO

USE master;GOIF DB_ID(N'ServiceHubProgrammabilityLab') IS NULL  CREATE DATABASE ServiceHubProgrammabilityLab;GOALTER DATABASE ServiceHubProgrammabilityLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubProgrammabilityLab;GOIF SCHEMA_ID(N'ops') IS NULL EXEC(N'CREATE SCHEMA ops AUTHORIZATION dbo;');IF SCHEMA_ID(N'api') IS NULL EXEC(N'CREATE SCHEMA api AUTHORIZATION dbo;');IF SCHEMA_ID(N'lab13') IS NULL EXEC(N'CREATE SCHEMA lab13 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'ops.WorkOrder',N'U') IS NULLBEGIN  CREATE TABLE ops.WorkOrder  (    work_order_id bigint IDENTITY(1001,1) NOT NULL      CONSTRAINT PK_ops_WorkOrder PRIMARY KEY,    customer_code varchar(16) NOT NULL,    region_code char(3) NOT NULL,    status varchar(16) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    closed_at datetime2(0) NULL,    amount decimal(12,2) NOT NULL,    description nvarchar(400) NULL,    CONSTRAINT CK_ops_WorkOrder_status      CHECK (status IN ('OPEN','ASSIGNED','CLOSED','ESCALATED')),    CONSTRAINT CK_ops_WorkOrder_priority CHECK (priority BETWEEN 1 AND 5)  );  ;WITH n AS  (    SELECT TOP (30000)      ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT ops.WorkOrder(customer_code,region_code,status,priority,opened_at,closed_at,amount,description)  SELECT CONCAT('CUST-',RIGHT('000000'+CONVERT(varchar(6),n%9000),6)),         CASE n%3 WHEN 0 THEN 'N01' WHEN 1 THEN 'W02' ELSE 'E03' END,         CASE WHEN n%23=0 THEN 'ESCALATED'              WHEN n%5=0 THEN 'CLOSED'              WHEN n%2=0 THEN 'ASSIGNED' ELSE 'OPEN' END,         CONVERT(tinyint,1+n%5),         DATEADD(minute,n,'2026-01-01T00:00:00'),         CASE WHEN n%5=0 THEN DATEADD(minute,n+60,'2026-01-01T00:00:00') END,         CAST(25+(n%25000)/10.0 AS decimal(12,2)),         CONCAT(N'ServiceHub work order ',n)  FROM n;  CREATE INDEX IX_ops_WorkOrder_region_status_opened    ON ops.WorkOrder(region_code,status,opened_at)    INCLUDE(customer_code,priority,amount,closed_at);END;GO

USE ServiceHubProgrammabilityLab;GOCREATE OR ALTER PROCEDURE api.FindWorkOrders  @region_code char(3) = NULL,  @status varchar(16) = NULL,  @max_rows int = 100,  @rows_returned int OUTPUTASBEGIN  SET NOCOUNT ON;  IF @max_rows NOT BETWEEN 1 AND 500  BEGIN    SET @rows_returned = 0;    RETURN 10;  END;  SELECT TOP (@max_rows)         work_order_id,customer_code,region_code,status,         priority,opened_at,closed_at,amount  FROM ops.WorkOrder  WHERE (@region_code IS NULL OR region_code=@region_code)    AND (@status IS NULL OR status=@status)  ORDER BY opened_at DESC,work_order_id DESC;  SET @rows_returned = @@ROWCOUNT;  RETURN 0;END;GO

The result set is application data. The output parameter is metadata about this invocation. The integer return value is a compact status code, not a substitute for returning data. An API client should not infer success merely because it received a rowset; define what each channel means.

DECLARE @rows int,@rc int;EXEC @rc = api.FindWorkOrders  @region_code='N01',  @status='OPEN',  @max_rows=5,  @rows_returned=@rows OUTPUT;SELECT @rc AS return_code,@rows AS rows_returned;GOEXEC sys.sp_describe_first_result_set  @tsql=N'EXEC api.FindWorkOrders @region_code=''N01'',@status=''OPEN'',@max_rows=5,@rows_returned=NULL';GO

2. Plans are reusable state, not part of the contract

On first execution SQL Server compiles statements in the procedure and can cache their execution plans. Subsequent calls can reuse those plans if they remain valid. Reuse depends on far more than the procedure name: schema/metadata changes, statistics-related recompiles, SET options, memory pressure, explicit recompile choices, and optimizer decisions all matter. A caller may rely on the result contract; it must not rely on a specific cached plan surviving.

SELECT OBJECT_SCHEMA_NAME(object_id,database_id) AS schema_name,       OBJECT_NAME(object_id,database_id) AS procedure_name,       cached_time,last_execution_time,execution_count,       total_worker_time,total_elapsed_timeFROM sys.dm_exec_procedure_statsWHERE database_id=DB_ID(N'ServiceHubProgrammabilityLab')  AND object_id=OBJECT_ID(N'api.FindWorkOrders');GOSELECT OBJECT_NAME(d.referencing_id) AS referencing_module,       d.referenced_schema_name,d.referenced_entity_nameFROM sys.sql_expression_dependencies AS dWHERE d.referencing_id=OBJECT_ID(N'api.FindWorkOrders');GO

These DMVs are transient: restart, eviction and recompilation can reset what you see. Query Store is the persistent workload evidence layer covered in Chapter 11. Use cache DMVs for current engine state, not as an audit history.

3. Transaction ownership and error semantics belong in the API design

A read-only lookup rarely needs an explicit transaction. A procedure that performs a business operation often does. Decide whether the procedure starts and completes its own transaction or expects an existing caller transaction. Blindly issuing ROLLBACK in a nested caller can undo work the procedure did not start.

CREATE OR ALTER PROCEDURE api.CloseWorkOrder  @work_order_id bigintASBEGIN  SET NOCOUNT ON;  SET XACT_ABORT ON;  DECLARE @started bit=0;  IF @@TRANCOUNT=0  BEGIN    BEGIN TRAN;    SET @started=1;  END;  BEGIN TRY    UPDATE ops.WorkOrder      SET status='CLOSED',closed_at=SYSUTCDATETIME()    WHERE work_order_id=@work_order_id      AND status<>'CLOSED';    IF @@ROWCOUNT=0 THROW 50013,'Work order was not found or is already closed.',1;    IF @started=1 COMMIT;  END TRY  BEGIN CATCH    IF XACT_STATE()=-1 ROLLBACK;    ELSE IF XACT_STATE()=1 AND @started=1 ROLLBACK;    THROW;  END CATCH;END;GO

This pattern is intentionally explicit about whether the procedure owns the transaction. In a broader application architecture you may instead require all transaction control at the service layer. The important requirement is one documented rule, not accidental nesting.

4. Ownership chaining: grant the operation, not the whole table

SQL Server checks permissions along an ownership chain. When a caller executes a module and the module accesses another object with the same owner, SQL Server can skip a second permission check on the referenced object. That enables a narrow pattern: grant EXECUTE on the procedure while withholding direct table access. It does not mean procedures automatically bypass security, and dynamic SQL or broken ownership chains change the behavior.

USE ServiceHubProgrammabilityLab;GOIF USER_ID(N'lab13_api_caller') IS NULL  CREATE USER lab13_api_caller WITHOUT LOGIN;GRANT EXECUTE ON OBJECT::api.FindWorkOrders TO lab13_api_caller;DENY SELECT ON OBJECT::ops.WorkOrder TO lab13_api_caller;GOEXECUTE AS USER=N'lab13_api_caller';SELECT USER_NAME() AS execution_user;-- Direct access should fail:BEGIN TRY SELECT TOP (1) * FROM ops.WorkOrder; END TRYBEGIN CATCH SELECT ERROR_NUMBER() AS direct_error,ERROR_MESSAGE() AS direct_message; END CATCH;-- Same-owner module access succeeds:DECLARE @rows int,@rc int;EXEC @rc=api.FindWorkOrders @region_code='W02',@max_rows=2,@rows_returned=@rows OUTPUT;SELECT @rc AS return_code,@rows AS rows_returned;REVERT;GO

The evidence proves only this chain under this ownership and module definition. Cross-database access, different owners, EXECUTE AS, certificates/module signing, and dynamic SQL have different security implications. Chapter 14 develops those mechanisms in depth.

5. Deployment dependencies and schema changes

Modules are schema objects, so deployment needs dependency awareness and tests. CREATE OR ALTER preserves permissions while updating the definition. Schema changes can invalidate cached plans or make modules fail at execution. SQL Server supports deferred name resolution for some procedure references, so “the procedure was created successfully” is not proof that every referenced object exists and every execution path works.

SELECT s.name AS schema_name,o.name,o.type_desc,m.uses_ansi_nulls,       m.uses_quoted_identifier,m.is_schema_boundFROM sys.objects AS oJOIN sys.schemas AS s ON s.schema_id=o.schema_idJOIN sys.sql_modules AS m ON m.object_id=o.object_idWHERE o.object_id IN (OBJECT_ID(N'api.FindWorkOrders'),OBJECT_ID(N'api.CloseWorkOrder'));GOEXEC sys.sp_refreshsqlmodule N'api.FindWorkOrders';GO

sp_refreshsqlmodule can refresh persistent metadata after underlying type/object changes, but it is not a substitute for automated deployment tests. A release pipeline should compile/deploy modules in dependency order, execute contract tests, verify permissions, and compare Query Store/runtime behavior after release.

Production judgment

Use procedures when you want a durable database API, permission boundary, transaction unit, reusable query bundle, or shared business operation. Keep parameters strongly typed, result contracts explicit, ownership documented, and error semantics predictable. Avoid using procedures as giant scripts that secretly create/drop infrastructure, depend on session state, or return a different schema for every branch unless the callers explicitly support that contract.

Mandatory labs require SQL Server 2025 Developer or Express, compatibility 170, CREATE PROCEDURE/ALTER on the lab schema, and appropriate DMV visibility for runtime diagnostics. No restart, cloud service, cluster, CLR, or paid production license is required.

Authoritative references

• CREATE PROCEDURE (Transact-SQL) — procedure syntax, parameters, RECOMPILE and permissions
• Recompile a stored procedure — procedure/statement recompile choices
• Ownership chains and context switching — module security behavior
• sys.dm_exec_procedure_stats — current cached procedure runtime counters
• SQL Server 2025 build versions — servicing baseline