How to get params optional in SQL

Question

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

Sample data, desired results, and an explanation of the logic would really help. — Gordon Linoff, Nov 18 '18 at 12:13
Possible duplicate of [optional parameters in SQL Server stored proc?](https://stackoverflow.com/questions/1810638/optional-parameters-in-sql-server-stored-proc) — Yann39, Nov 18 '18 at 12:18
And if you question is about passing a list of values as parameter see https://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure — Yann39, Nov 18 '18 at 12:19
This is very open to injection. I don't ever recommend co concatenating strings like this to create dynamic SQL. Especially as you're declaring `@TableName` as an `nvarchar(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 like `sysname` or `nvarchar(128)` and quote the object name using `QUOTENAME`. — Thom A, Nov 18 '18 at 12:23
Personally, I would suggest letting us know what you're really trying to achieve here so that we can help you stop the giant security hole you've got right now. — Thom A, Nov 18 '18 at 12:26

JohnLBevan · Answer 1 · 2018-11-18T12:44:26.900

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 want to delete all the rows but live one row, not remove all the rows — Ayal, Nov 18 '18 at 12:40
@Ayal, as mentioned in the comments there are various ways to do that. E,g, https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows, https://stackoverflow.com/questions/15053693/deleting-duplicate-record-in-sql-server. However, your question was about how to pass the column names, so I've only attempted to answer that part in this answer. — JohnLBevan, Nov 18 '18 at 12:42
I do not want to delete all the rows, I want to delete all rows -1 that means, leaving one line of all duplicates — Ayal, Nov 18 '18 at 12:55

score 0 · Accepted Answer · answered Nov 21 '18 at 13:56

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

How to get params optional in SQL

2 Answers2