Stored Procedure, When to Use Output Parameter VS Return Variable

When exactly do we use stored procedures with output parameters?

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.

SQL Server Performance ResultSet vs Output Parameter vs Return Value

Returning a scalar value is more efficient than a result set, the reason is result set carries lot more helper methods along with it, which makes it heavy thus increasing latency in transmission of the object from sql to C# code/routine.

In your method 3: You have used a variable to return the value this is more better than sending an out parameter since here you are cutting down on traverse of an object atleast in one route ( i.e., when invoking the stored procedure).

A result set is more flexible than an output parameter because it can return multiple rows (obviously), so if you need a result set then it's the only choice anyway.

To order the queries based on performance that goes as Method 3, Method 2 Method 1.

Hope this is helpful in understanding the concept.

Stored procedure (Input vs Output) vs Return - SQL Server

It's quite simple, actually:

Use input parameters to send data into the stored procedure.
Use Output parameters to return scalar values from the stored procedure.
Use a Select statement inside the procedure to return tabular data.
Don't use Return.¹ If you want to abort a stored procedure mid-process, throw an error instead.

¹ Well, it depends on the client, but usually, it's simpler to use errors than return codes, and also safer. There is no guarantee that the client will actually bother to check the return code, but if you throw an error, that can't be ignored.

However, there are probably a few times when it's better to return a number other than zero to indicate failure, but that means that whatever code that is using the procedure must be aware of the meaning of the numbers, which often leads to a terrible mess.

I once worked for a company that used this kind of thing, and I inherited a c# code containing an enum of approximately 300 members, all for error codes thrown from stored procedures - which means that every new error in any stored procedure we had to first make sure that error is not already in the enum, and if it's not, we had to recompile the c# code and re-deploy it even if we only changed an existing procedure.

Naturally, this was a maintenance nightmare.

Returning output parameter from stored procedure

You need to pass parameters, as neither have default values. As @p_prefix is an OUTPUT parameter, in order to consume that value, you need to declare a variable to pass to the parameter:

DECLARE @p_prefix varchar(3);
EXEC SFT.usp_GetPrefix @p_table = 'MAC_CHEESE', @p_prefix OUTPUT;

--PRINT @p_prefix; --Or whatever you're going to do with the value.

As a side note, you can make your Procedure considerably shorter:

CREATE PROCEDURE SFT.usp_GetPrefix @p_table  VARCHAR(30), @p_prefix VARCHAR(3) OUTPUT AS
BEGIN

    SET @p_prefix = CASE LEFT(@p_table,3) WHEN 'MAC' THEN 'MAC' ELSE 'UNK' END;

END

SQL Server stored procedure output parameter and return data from select

The simplistic answer is to add @managerID to your select statement

For a less simplistic perhaps the following, I changed the names a bit to reflect the use, get parameter after it closes.

var managerIDParam = new SqlParameter("@managerID", System.Data.SqlDbType.Int);
managerIDParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(managerIDParam);

using (var reader = cmd.ExecuteReader())
{
    tasks = reader.MapToList<TaskManagerTask>();
}
int managerIDParamUsed = (int)managerIDParam.Value;

Return variable from stored procedure call as output value for variable

Consider declaring an output parameter which from DB2 docs:

Output-only parameters, which return values from the stored procedure to the calling program.

CREATE PROCEDURE myProc (OUT V_R_ID  VARCHAR (20))

   LANGUAGE SQL 

P1 : BEGIN ATOMIC

   DECLARE V_H_ID INTEGER DEFAULT 0 ;
   DECLARE V_R_ID VARCHAR ( 20 ) ;
   DECLARE V_TIMESTAMP TIMESTAMP ;

   DECLARE C_NAME VARCHAR ( 45 ) DEFAULT 'EMAIL' ;

   SELECT CAST ( NEXT VALUE FOR SCHEMA . H_ID_SEQUENCE AS VARCHAR ( 20 ) )
   INTO V_R_ID
   FROM SYSIBM . SYSDUMMY1 ;

   CALL PROCEDURESCHEMA . INSERT_TYPE ( 
      V_R_ID
    , C_NAME
    , IN_DATA
    , NULL
    , NULL ) ;

END P1

Then in PHP, using PDO prepared statement, call the procedure with ? placeholder and bind it to a PHP variable:

$dbh = new PDO("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=****;" .
               "HOSTNAME=##.##.##.###;PORT=####;PROTOCOL=TCPIP;", "user", "pass");

$stmt = $dbh->prepare("CALL myProc(?)"); 
$stmt->bindParam(1, $return_id, PDO::PARAM_STR, 20); 
$stmt->execute();

echo $return_id;

Similarly but slightly different, with PHP's IBM DB2 module connection bind parameter to a string name of variable:

$conn = db2_connect($database, $user, $password);

$stmt = db2_prepare($conn, "CALL myProc(?)");
db2_bind_param($stmt, 1, "return_id", DB2_PARAM_OUT);
db2_execute($stmt);

echo $return_id;

Use/Benefit of output parameter in stored procedures

There are several good answers already, but one not mentioned is when you need the procedure to return more than one variable.

Example: Searching for the highest salary a procedure might also return the I.D. of the person and whether they are still employed.

How to set multiple output parameters in stored procedures

Finally, I was able to achieve the desired result with the below code :

    --Creating a table variable
    declare @TempTable table(ticketnum varchar(50))

    --Inserting values in table variable using procedure
    insert @TempTable
    select TICKETNUMBER 
        FROM TicketHistory th 
    WHERE TICKETTIME IN 
    (
        SELECT MAX(S2.TICKETTIME) 
        FROM TicketHistory S2 
        WHERE th.TICKETNUMBER = S2.TICKETNUMBER
    )  
        AND th.CURRENTSTATUS_ANALYST = 'Resolved'
        AND th.TICKETTIME < dateadd(day, -5, GETDATE())

    --Selecting values from table variable
    SELECT * from @TempTable
            
    DECLARE @ticket_number varchar(100)
    DECLARE cur CURSOR FOR SELECT ticketnum FROM @TempTable
    OPEN cur

    FETCH NEXT FROM cur INTO @ticket_number

    WHILE @@FETCH_STATUS = 0 
        BEGIN
            insert into TicketHistory (CURRENTSTATUS_ANALYST,TICKETNUMBER,PREVIOUSSTATUS_ANALYST,TICKETTIME,FIELD,CREATEDBY)
              values('Closed', @ticket_number, 'Resolved', CURRENT_TIMESTAMP, 'Status', 'User.Auto')
            FETCH NEXT FROM cur INTO @ticket_number
        END

    CLOSE cur    
    DEALLOCATE cur
        
    END