|
|
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
|