How to form a parameterized SQL query to find duplicates in a table.

How to query a SQL database for duplicates?

1. Figure out your parameters

   This needs to be reusable, so we’ll parameterize it. I figured I needed table name and parameter name, and that’s it. Maybe your stuff is more complicated (like adding exclusions or something).

2. Declare your variables

   These two are nicely configurable whenever you need to query a different table later on.
   
   DECLARE @columnName VARCHAR(50);
SET @columnName = 'Id';
DECLARE @tableName VARCHAR(50);
SET @tableName = 'Notifications';

3. We want to Group By the column and count the rows

   Group by the column name and count the results – something like this:
   
   DECLARE @query NVARCHAR(200);
set @query = 'SELECT ' + @paramName + ', COUNT(' + @paramName + ') FROM ' + @tableName + ' GROUP BY ' + @paramName +' HAVING COUNT(' + @paramName + ')>1;'
   Okay, so that’s a bit convoluted, but it’ll all come together nicely in the end, I assure you.

4. The end result?

   Below is the SQL that I came up with:
   
   DECLARE @paramName VARCHAR(50);
SET @paramName = 'Id';

DECLARE @tableName VARCHAR(50);
SET @tableName = 'MyTable';

DECLARE @query NVARCHAR(200);
set @query = 'SELECT ' + @paramName + ', COUNT(' + @paramName + ') FROM ' + @tableName + ' GROUP BY ' + @paramName +' HAVING COUNT(' + @paramName + ')>1;'
EXEC sp_executesql @query