I want to create a de-dupe (remove duplicates) records (actually Guest Profiles) from a table (TableA). The user can opt for 5 criteria (Surname, Name, Email, PostCode,Phone). This means it can create combinations or use all or leave some out etc

I am trying to create a Dynamic SQL (with sp_executesql to avoid injection) but the problem is that I do not know how many criteria will the user use in order for me to build a GROUP BY clause.

Name    Initial     Email   PostCode    Phone
Guest1  George      g@u.com 33333       777777
Guest1  George      l@i.com 22222       888888
Guest1  George      g@u.com 11111       222222
Guest2  George      g@u.com 22222       000000
  • You MUST have some identifying field, fields combination or expression. It must be used for grouping. If not then it is always possible that you'll join two different users into one.
    – Akina
    Oct 17 '19 at 13:54

You should be able to re-use the same variable that lets you pick your columns, like this:

    create procedure dynamic_group_by (@params nvarchar(max))


    declare @sql nvarchar(max)

    set @sql = 'select ' + @params + ' from profiles p 
    where email like ''%@%'' 
    group by ' + @params

    print sql

    exec sp_executesql @sql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.