Finding duplicates using parameters

Question

Whenever I look for duplicate values in a column, I write a little script like this:

SELECT COL1, COUNT(COL1)
FROM TABLE1 
GROUP BY COL1
HAVING COUNT(COL1) >1

It works, but I have to type. So, I decided to try parameters:

DECLARE @table VARCHAR(100)
DECLARE @column VARCHAR(100)

SET @table = 'TABLE1';
SET @column = 'COLUMN1';

SELECT @column, COUNT(@column)
FROM @table
GROUP BY @column
HAVING COUNT(@column) >1

SQL doesn't like this. It states: Msg 1087, Level 16, State 1, Line 34. Must declare the table variable "@table".

If I slightly modify to use the table name:

DECLARE @column VARCHAR(100)
SET @column = 'COLUMN1';

SELECT @column, COUNT(@column)
FROM TABLE1
GROUP BY @column
HAVING COUNT(@column) >1

I receive this error: Msg 164, Level 15, State 1, Line 51 Each GROUP BY expression must contain at least one column that is not an outer reference.

What am I doing wrong?

gotqn · Accepted Answer · 2017-05-18 08:50:41Z

2

I would probably use a dynamic query to accomplish something like this.

DECLARE @table VARCHAR(100)
DECLARE @column VARCHAR(100)
DECLARE @SQL NVARCHAR(max)

SET @table = 'TABLE1';
SET @column = 'COLUMN1';
SET @SQL = 
'SELECT ' + @column +' , COUNT( ' + @column + ')
 FROM ' + @table + '
GROUP BY ' + @column + '
HAVING COUNT(' + @column + ') >1'

EXEC sp_executesql @sql

edited May 18, 2017 at 8:50

gotqn

44k47 gold badges167 silver badges257 bronze badges

answered May 17, 2017 at 20:05

C Bell

3471 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Al Howarth Over a year ago

Thanks, great solution!

Collectives™ on Stack Overflow

Finding duplicates using parameters

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related