1

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      [email protected] 33333       777777
Guest1  George      [email protected] 22222       888888
Guest1  George      [email protected] 11111       222222
Guest2  George      [email protected] 22222       000000
Improve this question
1
  • 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
    Commented Oct 17, 2019 at 13:54

1 Answer 1

Reset to default
1

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))

    as

    declare @sql nvarchar(max)

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

    print sql

    exec sp_executesql @sql
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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