Remove Duplicate Record Form a Table in SQL

Use Partial Clause to remove duplicate records
delete from
(Select Colum1 , column2,

rownumber() over( partion by column1, column2

order by column1, Colum2
)Rowsnumbers
from DuplicatrecordTable) AliasTable

where AliasTable.Rowsnumbers>1

Reference:
sqlservercentral

Retrieve Distinct Rows of Table from Data View or Data Table

string[] str = new string[2];// string array is passed with column name of table
str[0] = "ColumnName1";
str[1] = "ColumnName2";
Datatable dt = //your tableobject
dt.DefaultView.ToTable(true, str);// return table of distinct rows

Avoid concurrent Execution of SP

Use sp_getapplock, sp_releaseapplock

EXECUTE sp_addmessage

@msgnum = 51001,

@severity = 16,

@msgtext = N'Resource NOT Available',

@lang = 'us_english',

@replace = REPLACE

Return to Top

CREATE PROCEDURE dbo.Employees_U_LastName

( @EmployeeID int,

@LastName varchar(20)

)

AS

BEGIN

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION

DECLARE @LockResult int

--sphelp sp_getapplock

EXECUTE @LockResult = sp_getapplock

@Resource = 'RepeatableRead_TRANSACTION',

@LockMode = 'Exclusive',

@LockTimeout = 0

IF @LockResult <> 0

BEGIN

ROLLBACK TRANSACTION

RAISERROR ( 51001, 16, 1 )

RETURN

END


-- All code between the use of sp_getapplock above,

-- and sp_releaseapplock below will be restricted to

-- only one user at a time.


-- Ten Second delay for Demonstration Purposes

WAITFOR DELAY '00:00:10'

-- Remove these three lines for 'Normal' use



UPDATE Employees

SET LastName = @LastName

WHERE EmployeeID = @EmployeeID

EXECUTE sp_releaseapplock

@Resource = 'RepeatableRead_TRANSACTION'

COMMIT TRANSACTION



END