Stored procedures and parameters

Define a boundary for repeatable data operations.

UnderstandStudy an examplePractise

Understand the concept

A procedure is a database program with input parameters and potentially several operations. Result sets, OUTPUT parameters and RETURN status codes are different output channels. SET NOCOUNT ON suppresses row-count messages, not results.

For atomic operations, design XACT_ABORT, error handling and transaction ownership. Unconditionally committing or rolling back a caller’s transaction can be incorrect. Procedures do not automatically prevent injection: concatenated dynamic SQL remains unsafe. Match parameter types to column types. Parameter sniffing describes compilation influenced by parameter values; do not add RECOMPILE without measuring.

WHEN TO USE IT

Organise parameterised report calls.

COMMON PITFALL

Expecting RETURN to return a whole table.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

DECLARE @CustomerId int = 1;
SELECT OrderId, OrderDate FROM lab.SalesOrder WHERE CustomerId = @CustomerId;

Shows a parameter used in a query.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Call GetCustomerOrders for customer two.

Show the solution and explanation

Worked solution

EXEC lab.GetCustomerOrders @CustomerId = 2;

Create the procedure first. Orders 102 and 108 are returned.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.