Stored procedure with output parameter using Aggregate functions

Other SQL Server Topics
1

Hi,
I am trying to create a procedure PROC_SUM_COUNTRY_OUTPUT with output parameter. But I am getting error. Can someone please advise where am I going wrong. Eventhough I tried simple procedure TEST_OUTPUT1 with output parameter and it worked.

CREATE PROCEDURE TEST_OUTPUT1
@COUNT1 VARCHAR(30) OUTPUT
AS
BEGIN
SELECT @COUNT1 = COUNT(*)
FROM TEST_SALES
END

DECLARE @COUNT_TEST VARCHAR(30)
EXECUTE TEST_OUTPUT1 @COUNT_TEST OUTPUT
PRINT @COUNT_TEST

GO
IF OBJECT_ID('PROC_SUM_COUNTRY_OUTPUT', 'P') IS NOT NULL
DROP PROCEDURE PROC_SUM_COUNTRY_OUTPUT;
GO

CREATE PROCEDURE PROC_SUM_COUNTRY_OUTPUT
@SALES_AGENT1 VARCHAR(30),
@SALES_COUNTRY1 VARCHAR(30),
@SALES_AMOUNT1 INT OUTPUT
AS
BEGIN
SELECT SALES_AGENT, SALES_COUNTRY, @SALES_AMOUNT1 = SUM(SALES_AMOUNT)
FROM TEST_SALES
GROUP BY SALES_AGENT, SALES_COUNTRY
HAVING SALES_AGENT = @SALES_AGENT1 AND SALES_COUNTRY = @SALES_COUNTRY1

END

I am getting below error message

Msg 141, Level 15, State 1, Procedure PROC_SUM_COUNTRY_OUTPUT, Line 7 [Batch Start Line 251]
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

1 Like
2

The error message tells you what the problem is. You cannot have assignment (@SALES_AMOUNT1 = SUM(SALES_AMOUNT)) and data-retrieval operations (the other two columns in your select) in the same SELECT statement. Change your stored proc to this.

IF OBJECT_ID('PROC_SUM_COUNTRY_OUTPUT', 'P') IS NOT NULL
    DROP PROCEDURE PROC_SUM_COUNTRY_OUTPUT;
GO

CREATE PROCEDURE PROC_SUM_COUNTRY_OUTPUT
    @SALES_AGENT1   VARCHAR(30),
    @SALES_COUNTRY1 VARCHAR(30),
    @SALES_AMOUNT1  INT OUTPUT
AS
    BEGIN
        SELECT
            @SALES_AMOUNT1 = SUM(SALES_AMOUNT)
        FROM
            TEST_SALES
        WHERE
            SALES_AGENT = @SALES_AGENT1
            AND SALES_COUNTRY = @SALES_COUNTRY1;

    END;
GO