SQL Interview: 43 No return value from calling procedure

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Developer Level: Intro

Question:

You want to call a procedure that counts the number of customers in a particular city.

You use the following code:

DECLARE @NumberOfCustomers int;

EXEC dbo.GetNumberOfCustomers 
    @City = N'Brisbane', 
    @NumberOfCustomers = @NumberOfCustomers;

SELECT @NumberOfCustomers;

No matter which city name you pass in the code, the selected count is NULL.

What is the basic problem here?

Answer:

For an output parameter to work when calling a SQL Server stored procedure, you must use the keyword OUTPUT after the value that is passed.

Here is an example:

DECLARE @NumberOfCustomers int;

EXEC dbo.GetNumberOfCustomers 
    @City = N'Brisbane', 
    @NumberOfCustomers = @NumberOfCustomers OUTPUT;

SELECT @NumberOfCustomers;

2025-05-04