SQL Server Interview Question #109

What is an OUTPUT parameter?

Views, Stored Procedures & User-Defined Functions Mid-Level Intermediate

Detailed Explanation

An OUTPUT parameter allows a stored procedure to return a scalar value through a parameter. The parameter must be declared with OUTPUT in the procedure definition, and the caller must also specify OUTPUT when capturing the returned value.

Output parameters are useful for returning values such as generated identifiers, counts, status codes, or calculated totals in addition to or instead of result sets.

Code Example

CREATE PROCEDURE dbo.GetProductCount
    @CategoryId INT,
    @ProductCount INT OUTPUT
AS
BEGIN
    SELECT @ProductCount = COUNT(*)
    FROM dbo.Products
    WHERE CategoryId = @CategoryId;
END;
GO

DECLARE @Count INT;

EXEC dbo.GetProductCount
    @CategoryId = 10,
    @ProductCount = @Count OUTPUT;

SELECT @Count AS ProductCount;