Returning Data from Stored Procedures
Stored procedures can return data to the caller in several ways. Understanding the options helps you choose the most appropriate method for your scenario.
1. Result Sets (SELECT statements)
A stored procedure can contain one or more SELECT statements that return result sets. Clients can process these as rows returned from a query.
CREATE PROCEDURE dbo.GetTopCustomers
@TopN INT = 10
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP(@TopN) CustomerID, CompanyName, TotalSales
FROM dbo.CustomerSales
ORDER BY TotalSales DESC;
END;
2. Output Parameters
Use OUTPUT parameters to return scalar values.
CREATE PROCEDURE dbo.GetCustomerCount
@Region NVARCHAR(50),
@CustomerCount INT OUTPUT
AS
BEGIN
SELECT @CustomerCount = COUNT(*)
FROM dbo.Customers
WHERE Region = @Region;
END;
3. Return Code
The RETURN statement sends an integer status code back to the caller.
CREATE PROCEDURE dbo.UpdateInventory
@ProductID INT,
@Quantity INT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM dbo.Products WHERE ProductID = @ProductID)
RETURN -1; -- Product not found
UPDATE dbo.Inventory
SET Quantity = Quantity + @Quantity
WHERE ProductID = @ProductID;
RETURN 0; -- Success
END;
Best Practices
- Prefer result sets for tabular data.
- Use output parameters for single values that need to be named.
- Reserve the return code for status information only.
- Always include
SET NOCOUNT ON;to avoid extra row‑count messages.