can we have a stored procedure with output parameters and return statement?

Question

Hi,Can a stored procedure have output parameters and return statement? If so can anybody give me a simple example.thank you all.

Answer 1

No. It is either a stored procedure or a scalar function.

A scalar function returns a value and takes 0 to n in parameters.

A stored procedure can take from 0 to n input parameters and can have 0 to n output parameters.

Answer 2

Stored procedure can return integer type only in a return statement and can have any number of out parameters. See this for references supporting this.

Simplest eg of stored procedure

Return integer value from SP

CREATE procedure [sys].[sp_name]
(
    @var1       bit = 0,
    @publisher  smallint                    
)
AS
BEGIN

    IF @var1<> 0
        RETURN (@publisher  )
 END

Using Out Parameter

CREATE PROCEDURE GetImmediateManager
   @employeeID INT,
   @managerID INT OUTPUT
AS
BEGIN
   SELECT @managerID = ManagerID 
   FROM HumanResources.Employee 
   WHERE EmployeeID = @employeeID
END

You can use Transactions, Exception handling(try Catch), DDL and DML Queries, calling another stored procedure within one stored procedures and many more operations.

Please comments for more functionalities supported in stored procedure

Answer 3

If you mean the standard RETURN statement that gives an integer then yes

If you mean a UDF RETURN , then no. But a stored proc can have a normal SELECT

Answer 4

You are free to use both OUTPUT params and a single RETURN value:

CREATE PROCEDURE RaiseToPower (
      @IN  INT, 
      @OUT INT OUTPUT
   )
AS
   DECLARE @POWER INT 
   SET @POWER = 3
   SET @OUT = POWER(@IN, @POWER)
   RETURN @POWER
GO

/**/
DECLARE @POW INT, @RESULT INT

EXEC @POW = dbo.RaiseToPower 2, @RESULT OUTPUT 

SELECT 2, 'raised to', @POW, 'is', @RESULT

>> 2 raised to 3 is 8