CODEWITHSUNDEEP
sqlsql-serverstored-procedures
sravas
sravas

Reputation: 41

Stored procedure :GROUP BY expression must contain at least one column that is not an outer reference

I created a stored procedure. @Sea is output parameter from the user. In Inline query it is executing but in stored procedure not getting below error.

Create Procedure [dbo].[procedure name]
(
    @APTSeason NVARCHAR(1000),
    @rptsummary NVARCHAR(1000),
    @Sea NVARCHAR(1000)
)
AS
BEGIN

    SELECT (@Sea),
        Sum(ProjectedRevenue) as 'Total_$',
        Sum(TotalSalesUnits) as 'Total_U',


    From  table name

    group by @Sea
END
Go

Error:

Each GROUP BY expression must contain at least one column that is not an outer reference

Upvotes: 1

Views: 232

Answers (2)

LONG
LONG

Reputation: 4610

I think you are looking for Dynamic SQL:

Create Procedure [dbo].[procedure name]
(
    @APTSeason NVARCHAR(1000),
    @rptsummary NVARCHAR(1000),
    @Sea NVARCHAR(1000)
)
AS
BEGIN

DECLARE @SQL VARCHAR(MAX)

SET @SQL = 'SELECT (' + QUOTENAME(@Sea) +'),
        Sum(ProjectedRevenue) as ''Total_$'',
        Sum(TotalSalesUnits) as ''Total_U'',


    From  table name

    group by ' + QUOTENAME(@Sea)

PRINT(@SQL)
--EXEC(@SQL)  --Comment this in if the previous "Print" outputs the correct SQL
END
Go

PS: I do not see the @APTSeason and @rptsummary are necessary here if they are not used anywhere in the SP.

Upvotes: 1

sumo
sumo

Reputation: 26

--GROUP BY CLAUSE SHOULD INCLUDE COLUMNS FROM THE SELECT QUERY WITHOUT ANY LITERALS OR CONSTANTS.

You can try the below steps to do a work around:

  1. Give @set an alias name with the as clause.
  2. Remove the group by clause having @set.

It will give the result without an error.

Upvotes: 0

Related Questions