SQL Server Error Messages - Msg 181

Error Message

Server: Msg 181, Level 15, State 1,
Procedure <Procedure Name>, Line 1
Cannot use the OUTPUT option in a DECLARE, CREATE AGGREGATE or CREATE FUNCTION statement.

Causes

The OUTPUT (or OUT for short) parameter option indicates that a parameter is an output parameter in a stored procedure. With this option specified on a parameter, the stored procedure can return the current value of the parameter to the calling program when the stored procedure exits.

The OUTPUT option can only be specified in a parameter to a stored procedure. It cannot be specified as an option to a local variable declared using the DECLARE statement. Depending on the version of SQL Server used, the following DECLARE statement with the OUTPUT option will generate an error with different error messages.

DECLARE @RowCount INT OUTPUT

Using SQL Server 2000, the following message will be received:

Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.

On the other hand, using SQL Server 2005, the following message will be received for the same DECLARE statement with the OUTPUT option:

Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE or CREATE FUNCTION statement.

Using SQL Server 2008, a totally different error message will be generated:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'OUTPUT'.

Based on the error message received from SQL Server 2000 and SQL Server 2005, the OUTPUT option cannot also be specified in the parameters of a user-defined function. To illustrate on how this error can be generated from a user-defined function, given the following table structure:

CREATE TABLE [dbo].[Sales] (
      [SalesID]               INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
      [SalesPersonID]         INT,
      [ProductID]             INT,
      [UnitCount]             INT,
      [SalesAmount]           MONEY,
      [SalesDate]             DATETIME
) 
GO

The following user-defined function, which returns the average sales amount for a given sales person (if provided) and for a given product (if provided) as well as the total sales amount and total units sold as output parameters, will generate a bunch of errors:

CREATE FUNCTION [dbo].[ufn_GetSales] (
      @SalesPersonID          INT,
      @ProductID              INT,
      @TotalCount             INT OUTPUT,
      @TotalAmount            MONEY OUTPUT )
RETURNS MONEY
AS
BEGIN
    DECLARE @AverageSales  MONEY

    SELECT @TotalCount = SUM([UnitCount]),
           @TotalAmount = SUM([SalesAmount])
    FROM [dbo].[Sales]
    WHERE ([SalesPersonID] = @SalesPersonID OR @SalesPersonID IS NULL) AND
          ([ProductID] = @ProductID OR @ProductID IS NULL)

    SET @AverageSales = @TotalAmount / NULLIF(@TotalCount, 0)

    RETURN @AverageSales
END 
GO

Msg 181, Level 15, State 1, Procedure ufn_GetSales, Line 4
Cannot use the OUTPUT option in a DECLARE, CREATE AGGREGATE or CREATE FUNCTION statement.
Msg 137, Level 15, State 1, Procedure ufn_GetSales, Line 11
Must declare the scalar variable "@TotalCount".
Msg 137, Level 15, State 2, Procedure ufn_GetSales, Line 17
Must declare the scalar variable "@TotalAmount".
Msg 178, Level 15, State 1, Procedure ufn_GetSales, Line 19
A RETURN statement with a return value cannot be used in this context.

Solution / Work Around

To avoid this error in a DECLARE statement make sure that the OUTPUT option is not specified. Only specify the OUTPUT option in a parameter of a stored procedure.

In the case the OUTPUT option in a user-defined function parameter, there are different ways of working around this. Given that there are 3 different outputs needed from the user-defined function shown earlier, the first option is to create 3 separate user-defined functions, one returning the total sales amount, another returning the total sales count and lastly another one returning the average sales amount.

CREATE FUNCTION [dbo].[ufn_GetTotalSalesAmount] (
    @SalesPersonID    INT,
    @ProductID        INT
)
RETURNS MONEY
AS
BEGIN
    DECLARE @TotalAmount    MONEY

    SET @TotalAmount = 0.00
    SELECT @TotalAmount = SUM([SalesAmount])
    FROM [dbo].[Sales]
    WHERE ([SalesPersonID] = @SalesPersonID OR @SalesPersonID IS NULL) AND
          ([ProductID] = @ProductID OR @ProductID IS NULL)

    RETURN @TotalAmount
END
GO

CREATE FUNCTION [dbo].[ufn_GetTotalSalesCount] (
    @SalesPersonID    INT,
    @ProductID        INT
)
RETURNS INT
AS
BEGIN
    DECLARE @TotalCount     INT

    SET @TotalCount = 0
    SELECT @TotalCount = SUM([UnitCount])
    FROM [dbo].[Sales]
    WHERE ([SalesPersonID] = @SalesPersonID OR @SalesPersonID IS NULL) AND
          ([ProductID] = @ProductID OR @ProductID IS NULL)

    RETURN @TotalCount
END
GO

CREATE FUNCTION [dbo].[ufn_GetAverageSalesAmount] (
    @SalesPersonID    INT,
    @ProductID        INT
)
RETURNS MONEY
AS
BEGIN
    DECLARE @AverageSales   MONEY

    SET @AverageSales = 0.00
    SELECT @AverageSales = SUM([SalesAmount]) / NULLIF(SUM([UnitCount]), 0)
    FROM [dbo].[Sales]
    WHERE ([SalesPersonID] = @SalesPersonID OR @SalesPersonID IS NULL) AND
          ([ProductID] = @ProductID OR @ProductID IS NULL)

    RETURN @AverageSales
END
GO

The disadvantage of using 3 different user-defined functions is that it will perform 3 separate SELECT statements to get the three different output values. To overcome this disadvantage, the second option that can be done is to use a stored procedure instead of a user-defined function.

CREATE PROCEDURE [dbo].[usp_GetSalesValues]
    @SalesPersonID    INT,
    @ProductID        INT,
    @TotalAmount      MONEY OUTPUT,
    @TotalCount       INT OUTPUT,
    @AverageAmount    MONEY OUTPUT
AS
    DECLARE @AverageSales  MONEY

    SELECT @TotalCount = SUM([UnitCount]),
           @TotalAmount = SUM([SalesAmount])
    FROM [dbo].[Sales]
    WHERE ([SalesPersonID] = @SalesPersonID OR @SalesPersonID IS NULL) AND
          ([ProductID] = @ProductID OR @ProductID IS NULL)

    SET @AverageSales = @TotalAmount / NULLIF(@TotalCount, 0)
GO

If it’s a user-defined function that is really required to be used and not a stored procedure, then instead of using a scalar function, a multi-statement table-valued function should be used instead.

CREATE FUNCTION [dbo].[ufn_GetSalesValues] (
    @SalesPersonID    INT,
    @ProductID        INT
) RETURNS @SalesValues TABLE (
    [TotalAmount]      MONEY,
    [TotalCount]       INT,
    [AverageAmount]    MONEY
)
AS
BEGIN
    INSERT INTO @SalesValues ( [TotalAmount], [TotalCount], [AverageAmount] )
    SELECT SUM([SalesAmount]), SUM([UnitCount]),
           SUM([SalesAmount]) / NULLIF(SUM([UnitCount]), 0)
    FROM [dbo].[Sales]
    WHERE ([SalesPersonID] = @SalesPersonID OR @SalesPersonID IS NULL) AND
          ([ProductID] = @ProductID OR @ProductID IS NULL)

    RETURN
END
GO