CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Rocket128
Rocket128

Reputation: 143

When exactly do we use stored procedures with output parameters?

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?

Upvotes: 3

Views: 12864

Answers (3)

Paul Karam
Paul Karam

Reputation: 4210

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.

Upvotes: 5

Adam Cox
Adam Cox

Reputation: 3661

Answer from ozz regarding paging does not make sense because there is no input param that implements a contraint on the number of records returned.

However, to answer the question... the results returned by these stored procedures are not the same. The first returns the record count of contacts in given city in the out param ContactsCount. While the count may also be recieved in the second implement through examining the reader.Rows.Count, the actual records are also made a available. In the first, no records are returned - only the count.

Upvotes: 1

ozz
ozz

Reputation: 5366

An example, based on yours would be if you introduced paging to the query.

So the result set is constrained to 10 items, and you use a total count out parameter to drive paging on a grid on screen.

Upvotes: -1

Related Questions