set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: WangXinXi
-- Create date: 2019-03-01
-- Description: 解决多条重复数据问题,重复只保留一条数据,
/*
带Where
[DeleteTbDataTrs] 'aaa',' WHERE name = ''a'' '
不带Where
[DeleteTbDataTrs] 'aaa',''
*/
-- =============================================
ALTER PROCEDURE [dbo].[DeleteTbDataTrs]
@tableName nvarchar(50), -- 表名
@whereStr nvarchar(50) -- where 语句
AS
Declare @tbzd nvarchar(max)
Declare @Sql nvarchar(max)
BEGIN
set @tbzd = (select stuff((
select ','+name
from syscolumns
WHERE id=Object_Id(''+@tableName+'') for xml path('')
),1,1,'') as name
)
set @Sql = 'DELETE T
FROM
(
SELECT
Row_Number () OVER (
Partition BY '+@tbzd+'
ORDER BY '+@tbzd+'
) AS RowNumber ,*
FROM
'+@tableName+'
'+@whereStr+'
) T
WHERE
T.RowNumber > 1;'
exec(@Sql)
END