I need to wite procedure that get name of table and list of name of fildes to compare and dalete all the duplicate rows that compare this fildes
How can I get list of params - optional?
CREATE PROCEDURE DeleteDuplicateRows
@tableName NVARCHAR(MAX) not null,
@nameFildes ???,
AS
BEGIN
DECLARE @sql NVARCHAR(MAX)
set @sql = 'delete from '+@tableName+
'where '+ ????
EXEC sp_executesql @Sql
END
I think you're looking for a way to pass multiple values; i.e. so you can give zero to many field names. For that, rather than looking at an option parameter you're better off passing an "array" which can have zero or greater length. To do that, you can declare your own type, with that type representing a table holding a single column of nvarchar characters, such as below:
create type StringArrayType as table
(
value nvarchar(max) not null
)
go
create procedure DeleteDuplicateRows
@tableName NVARCHAR(MAX),
@fieldNames StringArrayType readonly
AS
begin
DECLARE @sql NVARCHAR(MAX)
select @sql = coalesce(@sql + ', ', '') + quotename(value)
from @fieldNames
--this deletes all values of any row with duplicates (i.e. including the original)
--To only delete the duplicates and not the original, look at using row_number() or similar
--(many examples exist on SO already, and that's not what you've asked about, so I'll only provide this version for this example)
set @sql = 'delete from ' + quotename(@tableName) + coalesce(' group by ' + @sql + ' having count(1) > 1','')
select @sql SqlStatement --for now just return the SQL
--EXEC sp_executesql @Sql
end
go
declare @fields StringArrayType;
insert @fields (value) values ('One'),('Two');
exec DeleteDuplicateRows 'myTable', @fields;
A Fiddle of this code can be found here: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=477266854bcefc73286868e27c882ee9
If you don't want to specify any fields, just skip the insert @fields (value) values ('One'),('Two'); statement; i.e. passing in an empty table.
I create this procedure that delete duplicate rows but leave one
`CREATE PROCEDURE [dbo].[DeleteDuplicateRows]
@TableName NVARCHAR(MAX) ,
@FildesName NVARCHAR(MAX)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX)
set @sql='DELETE FROM '+@TableName +' WHERE Id IN (
SELECT Id FROM (
SELECT
Id
,ROW_NUMBER() OVER (PARTITION BY '+ @FildesName +' ORDER BY Id) AS [ItemNumber]
FROM '+@TableName +
') a WHERE ItemNumber > 1 )'
EXEC sp_executesql @sql
END`
In @TableName - the name of the table.
in @FildesName - Key fields for double row
@TableNameas annvarchar(MAX); it's like you're asking for injection and letting who ever does it do whatever they want. At least use an appropriate data type likesysnameornvarchar(128)and quote the object name usingQUOTENAME.