When exactly do we use stored procedures with output parameters?

Question

When exactly do we use stored procedures with output parameters and when do we use stored procedures without parameters?

I base my question on an example:

Stored procedure with output parameter

CREATE PROCEDURE uspGetContactsCountByCity
    @City nvarchar(60),
    @ContactsCount int OUT
AS
BEGIN
    SELECT @ContactsCount = COUNT(ContactID) 
    FROM Contacts
    WHERE City = @City
END

Stored procedure executing

DECLARE @ContactsTotal INT

EXEC uspGetContactsCountByCity @ContactsCount = @ContactsTotal OUT, @city = 'Berlin'
SELECT @ContactsTotal

Results: 2

Stored procedure without output parameter

CREATE PROCEDURE uspGetContactsCountByCity2
    @City nvarchar(60)
AS
BEGIN
    SELECT COUNT(ContactID) 
    FROM Contacts
    WHERE City = @City
END

Stored procedure executing:

EXEC uspGetContactsCountByCity2 @city = 'Berlin'

Results: 2

Both procedures return the same result, in same form, so what's the difference?

Answer 1

Basically, the result you're seeing is actually the result of your SELECT at the end of the procedure, which is doing the same thing.

Please take a look at this documentation:

If you specify the OUTPUT keyword for a parameter in the procedure definition, the stored procedure can return the current value of the parameter to the calling program when the stored procedure exits. To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the stored procedure.

So basically if you would like your stored procedure to just return just a value instead of a data set, you could use the output parameter. For example, let's take the procedures you have given as an example. They both do the same thing, this is why you got the same result. But what about changing a little bit in the first procedure that has the output parameter. Here's an example:

create table OutputParameter (
    ParaName varchar(100)
)

insert into OutputParameter values ('one'), ('two'),('three'),('one')

CREATE PROCEDURE AllDataAndCountWhereOne
    @name nvarchar(60),
    @count int OUT
    as
    Begin
    SELECT @count = COUNT(*) from OutputParameter
    Where ParaName = @name

    select Distinct(ParaName) from OutputParameter
End

Declare @TotalCount int
Exec AllDataAndCountWhereOne @count = @TotalCount OUT, @name = 'One'
Select @TotalCount

With this example, you are getting all the distinct stored data in the table, plus getting the count of a given name.

ParaName
--------------------
one
three
two

(3 row(s) affected)


-----------
2

(1 row(s) affected)

This is one way of using the output parameter. You got both the distinct data and the count you wanted without doing extra query after getting the initial data set.

At the end, to answer your question:

Both procedures gives us the same result, in same form, so what's the difference?

You didn't make a difference in your own results, this is why you didn't really notice the difference.

Other Examples:

You could use the OUT parameter in other kinds of procedures. Let's assume that your stored procedure doesn't return anything, it's more like a command to the DB, but you still want a kind of message back, or more specifically a value. Take these two examples:

CREATE PROCEDURE InsertDbAndGetLastInsertedId
    --This procedure will insert your name in the database, and return as output parameter the last inserted ID.
    @name nvarchar(60),
    @LastId int OUT
    as
    Begin
    insert into OutputParameterWithId values (@name); 
    SELECT @LastId = SCOPE_IDENTITY()
End

or:

CREATE PROCEDURE InsertIntoDbUnlessSomeLogicFails
    --This procedure will only insert into the db if name does exist, but there's no more than 5 of it
    @name nvarchar(60),
    @ErrorMessage varchar(100) OUT
    as
    Begin
    set @ErrorMessage = ''

    if ((select count(*) from OutputParameterWithId) = 0)
    begin
        set @ErrorMessage = 'Name Does Not Exist'
        return
    end

    if ((select count(*) from OutputParameterWithId) = 5)
    begin
        set @ErrorMessage = 'Already have five'
        return
    end

    insert into OutputParameterWithId values (@name); 
End

These are just dummy examples, but just to make the idea more clear.