Jack Jack - 2 months ago 10
SQL Question

spx created but needs it to be dynamic

I have creaed this spx but i need it to be dynamic, dynamic in the sense is that it should accept

tablename
columns
orderby columns

so i can make this spx to work globally accross all tables, just to say here i am not sending the pk of the tabl whose entry i will be remooving

ALTER PROCEDURE [dbo].[spx_DeleteDuplicates]
AS
BEGIN
WITH DupsNumbered
AS (
SELECT [columns List],
ROW_NUMBER() OVER (PARTITION BY [columns List] ORDER BY [orderList]) AS rn
FROM [table]
)
DELETE DupsNumbered
WHERE rn > 1;
END


all the items you see in
[]
needs to be dynamic, how can i make it dynamic

Answer

Not a huge supporter of dynamic SQL, but if you must

ALTER PROCEDURE [dbo].[spx_DeleteDuplicates] (@TableName varchar(100),@ColumnList varchar(max),@OrderList varchar(max))
AS
BEGIN
    Declare @SQL varchar(max)='
      WITH DupsNumbered
        AS (
        SELECT [ColumnsList],
        ROW_NUMBER() OVER (PARTITION BY [ColumnsList] ORDER BY [OrderList]) AS rn
        FROM [TableName]
        )
        DELETE DupsNumbered
        WHERE rn > 1;
    '
    Set @SQL=Replace(@SQL,'[TableName]'  ,@TableName)
    Set @SQL=Replace(@SQL,'[ColumnsList]',@ColumnList)
    Set @SQL=Replace(@SQL,'[OrderList]'  ,@OrderList)
    Exec(@SQL)
END