SQL procedure group by parameter value

I want to use group by functionality with dynamic value.

Ex:

CREATE PROCEDURE getEmpStats (@COLUMN_NAME VARCHAR(20))
AS
BEGIN
    DECLARE @DUPLICATE_COUNTS INT

    BEGIN
        SELECT @DUPLICATE_COUNTS = SUM(DUP_COUNTS)
        FROM 
            (SELECT COUNT(*) AS DUP_COUNTS
             FROM emp
             GROUP BY @COLUMN_NAME) AS A

        -------------------
    END
END

When I code like this I get the error == >

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

Any workaround for this?

Answer

You need to be very careful with this type of code as it is prone to SQL injection.

Always use QUOTENAME on identifiers. And if the column name is coming from user code then verify it.

Because @DUPLICATE_COUNTS is not in scope in the dynamic part, you need to re-declare it as an OUTPUT parameter.

CREATE PROCEDURE getEmpStats (@COLUMN_NAME varchar(20))
AS

DECLARE @DUPLICATE_COUNTS int;

DECLARE @sql nvarchar(max) = N'

        SELECT @DUPLICATE_COUNTS = SUM(DUP_COUNTS)
        FROM 
            (SELECT COUNT(*) AS DUP_COUNTS
             FROM emp
             GROUP BY ' + QUOTENAME(@COLUMN_NAME) + N') AS A;
';

EXEC sp_executesql @sql, N'@DUPLICATE_COUNTS int OUTPUT', @DUPLICATE_COUNTS = @DUPLICATE_COUNTS

GO

To verify the column name, use this code:

IF (NOT EXISTS (SELECT 1
    FROM sys.columns c
    WHERE c.name = @COLUMN_NAME AND c.object_id = OBJECT_ID(N'emp')
    ))
    THROW 50000, 'Non existent column', 0;