CODEWITHSUNDEEP
sqlt-sql
Al Howarth
Al Howarth

Reputation: 83

Finding duplicates using parameters

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?

Upvotes: 1

Views: 45

Answers (1)

C Bell
C Bell

Reputation: 347

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

Upvotes: 2

Related Questions