In SQL Server, how does a stored procedure hand values back through OUTPUT parameters and a RETURN code, and what must the caller write?
answer
- keyword at both ends
- the caller can forget it
- EXEC @rc = procedure
- integer status only, never NULL
basics
~20 sA SQL Server procedure marks a parameter OUTPUT, and the caller must also pass a variable marked OUTPUT or the value is silently lost. RETURN sends back only an integer status, read with EXEC @rc = procedure.
solid answer
~50 sA procedure has three ways to hand data back: result sets, `OUTPUT` parameters and a return code. An `OUTPUT` parameter needs the keyword twice, in `CREATE PROCEDURE` and again at the call (`@Total = @t OUTPUT`). If the caller leaves it off, SQL Server raises no error and the caller's variable keeps its old value. The opposite mistake, `OUTPUT` at the call but not in the definition, does raise an error. `RETURN` carries only an `int`, captured with `EXEC @rc = dbo.MyProc`. Returning NULL gives the caller 0 and a warning. Return codes are a status convention. Failures should raise an error, and data should go back through `OUTPUT` parameters or result sets. I also put `SET NOCOUNT ON` first in the body, which stops the per-statement row-count messages. `@@ROWCOUNT` still works with it on.
code
sql · 27 linesCREATE TABLE dbo.Orders (
OrderId INT IDENTITY PRIMARY KEY,
CustomerId INT NOT NULL,
Amount DECIMAL(12, 2) NOT NULL
);
GO
CREATE OR ALTER PROCEDURE dbo.usp_PlaceOrder
@CustomerId INT,
@Amount DECIMAL(12, 2),
@OrderId INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
IF @Amount <= 0
RETURN 1; -- status for the caller; real failures should raise an error
INSERT INTO dbo.Orders (CustomerId, Amount)
VALUES (@CustomerId, @Amount);
SET @OrderId = CAST(SCOPE_IDENTITY() AS INT);
RETURN 0;
END;
GO
DECLARE @rc INT, @newId INT;
EXEC @rc = dbo.usp_PlaceOrder
@CustomerId = 42,
@Amount = 19.99,
@OrderId = @newId OUTPUT;
SELECT @rc AS return_code, @newId AS order_id;go deeper
Recall that OUTPUT is written in the procedure definition and again at the call, and that RETURN gives back only an integer captured with EXEC @rc = ....
Explain the silent case: omitting OUTPUT at the call raises no error and leaves the variable unchanged. Also explain that a NULL return becomes 0 with a warning, and that @@ROWCOUNT survives SET NOCOUNT ON.
Show review judgment: return identity values and totals through OUTPUT parameters, signal failure with errors rather than status codes, and flag callers that ignore a return code they depend on.
Treat the procedure's parameter list and result shape as a contract other teams code against, and set a team convention for how procedures report success, failure and data.
## Three ways out of a SQL Server procedure A **stored procedure** in SQL Server is a named T-SQL module created with `CREATE PROCEDURE`. A procedure can hand data back to its caller in three distinct ways, and each has its own syntax and limits: | Channel | What it carries | How the caller receives it | |---|---|---| | Result sets | Any number of rows and columns, from each `SELECT` the body runs | The client reads them as query results | | `OUTPUT` parameters | One scalar value per parameter (or a cursor) | A variable passed with the `OUTPUT` keyword | | Return code | One `int` | `EXEC @rc = schema.procedure` | Getting the mechanics of the last two right is everyday work in a SQL Server shop, and both have silent failure modes. ## OUTPUT parameters: the keyword belongs on both sides A parameter becomes an output parameter when the definition declares it with `OUTPUT` (or its synonym `OUT`). The rule that trips people up is that **the caller must say `OUTPUT` too**: - `EXEC dbo.usp_GetTotal @CustomerId = 7, @Total = @t OUTPUT;` copies the final value of `@Total` back into `@t` when the procedure exits. - `EXEC dbo.usp_GetTotal @CustomerId = 7, @Total = @t;` **also succeeds**, but nothing is copied back. No error is raised, and `@t` keeps whatever it held before. - Marking a parameter `OUTPUT` at the call when the definition did not declare it as output is the one direction that **does** raise an error. Other rules worth knowing: 1. An output parameter is also an input. Whatever the caller's variable holds is passed in, and the procedure may read and change it before handing it back. The documentation calls this pass-by-reference behaviour. 2. Names need not match: with positional arguments the data type and position must match, and with `@Param = @variable` the name does the binding. 3. A table-valued parameter cannot be `OUTPUT`. It is input-only and must be declared `READONLY`. 4. A procedure may declare up to **2,100** parameters. A default must be a constant or `NULL`, never a function call such as `GETDATE()`. ## Return codes: an integer status, nothing more `RETURN` ends the procedure and can hand back one integer expression. The caller has to capture it in a variable, using the form `EXEC @rc = dbo.usp_Something;`. Facts that matter: - Only `int` values. You cannot return a string or a decimal this way. - **A procedure cannot return NULL.** If it runs `RETURN @status` while `@status` is NULL, SQL Server issues a warning and the caller receives `0`. - By convention `0` means success. System stored procedures return `0` on success and a non-zero value on failure unless documented otherwise. - Current guidance is that a procedure should **signal failure by raising an error**, not with a return code alone, and should not use the return code to carry application data. Error handling with `TRY...CATCH` and `THROW` is part of the T-SQL dialect lesson. The point here is only that a return code is easy to ignore, and an error is not. ## SET NOCOUNT ON and other module habits `SET NOCOUNT ON` as the first statement after `AS` stops SQL Server from sending a row-count message (the `DONEINPROC` token, shown as "rows affected") for every statement in the procedure. Two details are worth getting right: - `@@ROWCOUNT` **is still updated** with `NOCOUNT` on, so logic that checks affected rows keeps working. - The size of the benefit depends on the workload. The `SET NOCOUNT` page describes a significant reduction in network traffic for procedures that run many statements or loops and return little data. The `CREATE PROCEDURE` guidance says there is no measurable benefit on today's hardware for typical procedures. It is a cheap habit, not a tuning trick. Two more habits come from the same guidance: schema-qualify every object name (`dbo.Orders`, not `Orders`) so name resolution is cheap and not tied to the caller's default schema, and list the columns you need rather than `SELECT *`, so a procedure does not break when a table's columns change. ## Mistakes interviewers listen for - Calling a procedure without `OUTPUT` at the call site, then puzzling over why the variable never changes. - Using the return code to send back an identity value or a total. Those belong in an `OUTPUT` parameter. - Assuming a NULL return reaches the caller as NULL. - Believing `SET NOCOUNT ON` stops `@@ROWCOUNT` from working. The code example creates a table and a procedure, then calls it with both an `OUTPUT` parameter and a return-code capture.
- In SQL Server, can a caller pass a starting value into an OUTPUT parameter?Yes. An `OUTPUT` parameter is also an input. The caller's variable value goes in, the procedure can read and modify it, and the final value is copied back when the procedure exits. The documentation calls this pass-by-reference behaviour. This only works when the caller also writes `OUTPUT` at the call site.
- Why does SQL Server guidance say not to rely on return codes to signal procedure failure?A return code is only seen if the caller captures it with `EXEC @rc = ...` and checks it, so a forgotten check makes a failure look like success. Raising an error with `THROW` or `RAISERROR` cannot be ignored that easily: it reaches the client and can drive transaction handling. Return codes are best kept as simple status values, never as application data.
- In SQL Server, what is the maximum number of parameters a stored procedure can declare, and what may a default be?A procedure can declare up to 2,100 parameters. A default must be a constant or `NULL`, and it may contain wildcards for use with `LIKE`. A function call such as `GETDATE()` cannot be a default or be passed directly as an argument. Assign it to a variable first and pass the variable.
- Leaving OUTPUT off the call raises an error, so the mistake is caught
- A procedure can RETURN a string or a decimal value
- RETURN NULL reaches the caller as NULL
- SET NOCOUNT ON stops @@ROWCOUNT from being updated
- Return codes are the recommended way to report procedure failure