SQL Server / Intermediate / Lesson 11

Stored procedures and typed parameters

Create a clear database API with predictable result shapes and error behavior. Keep parameterized dynamic SQL distinct from unsafe string construction.

About 5 min readContent reviewed 2026-10-23

On this page

Lesson goals

Describe procedure input, output, and transaction contracts.
Use sp_executesql with bound values.
Explain batch requirements and parameter sensitivity.

Treat a procedure as an API

A stored procedure packages T-SQL behind a named interface. Define input types, optional defaults, result columns, ordering, output parameters, and error behavior before implementation. A return status integer is not the same as a result set or an output parameter. Changing a column name or adding an unexpected SELECT can break a caller that reads result sets in sequence.

Procedures can centralize permissions and logic, but they do not automatically make code fast, atomic, or injection-safe. A procedure that concatenates caller text into an executable string still has an injection problem. Keep responsibilities small enough to review, document transaction ownership, and avoid returning diagnostic result sets on a successful application path.

Observe module creation boundaries

CREATE OR ALTER PROCEDURE is supported by the SQL Server 2019 baseline; it was introduced in SQL Server 2016 SP1. Procedure creation has batch requirements. A GO-aware tool can separate definition from later EXEC calls, while an application driver must send separate batches without GO. The example uses a local temporary procedure to illustrate a complete interface without a permanent business object.

SET NOCOUNT ON suppresses affected-row count messages; it does not hide SELECT results or change @@ROWCOUNT semantics. Explicit parameter types prevent accidental text-date interpretation and make input limits visible. Validate domain rules such as a maximum page size in addition to type checking. A valid int can still be an unreasonable request.

sql A temporary procedure and a separate execution batch

CREATE PROCEDURE #TrainingDouble @value int
AS
BEGIN
 SET NOCOUNT ON;
 SELECT @value*2 AS doubled;
END;
GO
EXEC #TrainingDouble @value=6;
DROP PROCEDURE #TrainingDouble;

Expected output

doubled
12

The tool consumes GO. The procedure returns one named numeric column, and the caller removes the temporary module.

Bind dynamic values correctly

sp_executesql accepts Unicode command text, a parameter-definition string, and separately bound parameter values. Merely calling it does not make concatenated input safe. The parameter must appear as a parameter marker in stable command text, with its value passed separately. The example returns a name containing an apostrophe without any manual quote manipulation.

Ordinary value parameters cannot stand for a table name, column name, or ASC/DESC keyword. If query structure must vary, select from a fixed application-owned mapping and quote approved identifiers appropriately. QUOTENAME helps delimit an identifier; it is not a business authorization policy. Keep identifier selection and value binding as two distinct review questions.

sql A value remains data inside dynamic SQL

DECLARE @name nvarchar(40)=N'O''Neil';
EXEC sys.sp_executesql
 N'SELECT @n AS display_name, LEN(@n) AS characters;',
 N'@n nvarchar(40)', @n=@name;

Expected output

display_name characters
O'Neil       6

The embedded apostrophe is part of a bound string. It does not terminate or extend the dynamic command.

Make errors and ownership predictable

A write procedure should say whether it starts its own transaction or requires a caller-owned one. Preserve original errors with THROW after required cleanup. Translate known business failures at an appropriate boundary, and keep database diagnostic details out of ordinary user messages. Do not use PRINT as a reliable structured error channel.

Result sets should remain stable across success paths, while failure should be unambiguous to the driver. Validate required values, lengths, and ranges before changing rows. A zero-row update may be a legitimate no-op, an optimistic conflict, or a missing entity; choose and document one meaning. The procedure name alone cannot tell a caller which interpretation applies.

Review reuse without promising one perfect plan

Stable parameterized statements promote plan reuse, reducing repeated compilation. A plan compiled for a rare customer can be unsuitable for a customer owning millions of rows. This parameter sensitivity is a distribution problem, not a reason to concatenate values. Compare representative parameter classes and execution evidence before adding RECOMPILE or other hints.

Keep deployment scripts separate from runtime calls, version their interface changes, and test callers against the exact result shape. Exercise null, boundary, apostrophe, and multilingual values. SQL Server-specific procedure semantics, batch creation, and typed parameter binding distinguish this API from generic SQL snippets copied from another database product.

Common errors and fixes

Incorrect syntax near GO in application code.

Why it happens: A deployment delimiter was sent as server syntax.
How to fix it: Send the procedure definition and execution as separate commands.
Prevent it next time: Separate script tooling from runtime APIs.

Parameterized procedure remains injectable.

Why it happens: It concatenates a parameter into executable text.
How to fix it: Use parameter markers and separately bound sp_executesql values.
Prevent it next time: Review the final execution boundary.

Caller reads the wrong result set.

Why it happens: A debug SELECT changed the procedure output sequence.
How to fix it: Remove diagnostic result sets and version the API contract.
Prevent it next time: Test result count, columns, and types.

Check your understanding

1. Can @sortColumn replace a column name in ORDER BY @sortColumn?

Show worked solution

That orders by a parameter value, not a dynamically chosen column identifier. Select from approved SQL fragments or use a reviewed CASE design, and keep ordinary filter values bound separately.

2. A procedure receives an apostrophe-containing customer name. Should the client remove apostrophes?