Stored procedures, safe dynamic SQL and parameter sniffing

A stored procedure is a named batch of T-SQL stored in the database, with parameters. Its statements are compiled when it is first run, and the plans are cached for later calls.

CREATE OR ALTER PROCEDURE dbo.add_order
  @customer_id INT,
  @total       INT,
  @new_id      INT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;               -- no "n rows affected" messages
  IF @total <= 0 RETURN 1;      -- status code, 0 means success
  INSERT INTO orders (customer_id, total) VALUES (@customer_id, @total);
  SET @new_id = SCOPE_IDENTITY();
  RETURN 0;
END;

Three ways to get data out:

• Result sets — any SELECT; capture one with INSERT INTO #t EXEC dbo.proc.
• OUTPUT parameters — the caller must write OUTPUT too: EXEC dbo.add_order 7, 120, @id OUTPUT;. Without it, the procedure still runs, and the caller's variable is not updated.
• Return code — EXEC @rc = dbo.add_order ...; an integer meant for status, not data.

1CREATE TABLE orders (id INT IDENTITY(1,1) PRIMARY KEY, customer_id INT NOT NULL, total INT NOT NULL);2GO3CREATE PROCEDURE dbo.add_order @customer_id INT, @total INT, @new_id INT OUTPUT4AS5BEGIN6  SET NOCOUNT ON;7  IF @total <= 0 RETURN 1;8  INSERT INTO orders (customer_id, total) VALUES (@customer_id, @total);9  SET @new_id = SCOPE_IDENTITY();10  RETURN 0;11END;

1DECLARE @log TABLE (call_no INT, call NVARCHAR(40), rc INT, new_id INT);2DECLARE @rc INT, @id INT;3 4SET @id = -1;5EXEC @rc = dbo.add_order 7, 120, @id OUTPUT;6INSERT INTO @log VALUES (1, N'7, 120, @id OUTPUT', @rc, @id);7 8SET @id = -1;9EXEC @rc = dbo.add_order 7, 80, @id;10INSERT INTO @log VALUES (2, N'7, 80, @id (no OUTPUT)', @rc, @id);11 12SET @id = -1;13EXEC @rc = dbo.add_order 7, 0, @id OUTPUT;14INSERT INTO @log VALUES (3, N'7, 0, @id OUTPUT', @rc, @id);15 16SELECT call, rc, new_id, (SELECT COUNT(*) FROM orders) AS orders_now17FROM @log18ORDER BY call_no;

Dynamic SQL without injection

Building SQL by concatenating user input lets the input rewrite the query. sp_executesql takes the statement and its parameters separately, so a value can only ever be a value. Table and column names cannot be parameters; validate them against a list and wrap them in QUOTENAME().

EXEC sp_executesql
  N'SELECT id, name FROM customers WHERE name = @name',
  N'@name NVARCHAR(50)',
  @name = @input;

1CREATE TABLE customers (id INT PRIMARY KEY, name NVARCHAR(50) NOT NULL);2INSERT INTO customers VALUES (1, N'Nino'), (2, N'Luka'), (3, N'Ana');3GO4CREATE PROCEDURE dbo.find_concat @name NVARCHAR(100)5AS6BEGIN7  SET NOCOUNT ON;8  DECLARE @sql NVARCHAR(400) = N'SELECT id FROM customers WHERE name = ''' + @name + N'''';9  EXEC (@sql);10END;11GO12CREATE PROCEDURE dbo.find_param @name NVARCHAR(100)13AS14BEGIN15  SET NOCOUNT ON;16  EXEC sp_executesql N'SELECT id FROM customers WHERE name = @n', N'@n NVARCHAR(100)', @n = @name;17END;

1DECLARE @input NVARCHAR(100) = N'x'' OR 1=1 --';2CREATE TABLE #concat (id INT);3CREATE TABLE #param (id INT);4INSERT INTO #concat EXEC dbo.find_concat @input;5INSERT INTO #param EXEC dbo.find_param @input;6 7SELECT 'concatenated' AS version, COUNT(*) AS rows_returned FROM #concat8UNION ALL9SELECT 'sp_executesql', COUNT(*) FROM #param;

Parameter sniffing

This section is explained, not demonstrated: the sandbox runs each example once on a few rows, so it cannot show a cached plan going stale. Read it as definitions and well-known behaviour.

When a procedure is first executed, SQL Server compiles its statements for the parameter values of that call ("sniffs" them) and caches the plan. Later calls reuse it. That is usually a win: no compile per call. It goes wrong when the data is skewed: a plan compiled for a customer with 3 orders (seek plus lookups) is reused for a customer with 3 million, or the other way round. The symptom is a procedure that is fast for some calls, slow for others, and "fixed" by a restart or a recompile until it breaks again.

The options, with their costs:

• OPTION (RECOMPILE) on the statement: a plan for every call's values; costs a compile each time.
• OPTION (OPTIMIZE FOR (@p = value)): always plan for a chosen typical value.
• OPTION (OPTIMIZE FOR UNKNOWN): plan from average density instead of the sniffed value; predictable but rarely ideal.
• Separate procedures or dynamic SQL for the very different cases.
• SQL Server 2022 at compatibility level 160 has parameter sensitive plan optimization switched on by default: it can cache several plans for one parameterized statement, but only for equality predicates on columns whose statistics show heavy skew.

Copying parameters into local variables also stops sniffing, with much the same effect as OPTIMIZE FOR UNKNOWN, but it hides the intent.

Common mistakes

• Forgetting OUTPUT at the call site. The variable keeps its old value; the first example shows the difference.
• Leaving an OUTPUT parameter unassigned on a failure path. The caller gets back whatever it passed in: -1 in the third call above. Set it to NULL before returning early.