Delete Multiple Integers as Parameter from an Int Column

--Delete multiple Integers as Parameter from an Int column
create proc deleteValues 
@IDS varchar(100)
delete Info where charindex( ','+ CAST(ID as varchar(5))+',' ,','+@IDS+',')>0

create table info (id int, col int)
Insert into info values(1,1),(2,1),(5,1),(6,1),(7,1),(8,1),(9,1),(3,1)

declare @s varchar(100)
set @s='5,6,7,8'
exec deletevalues @s
select * from info

--Clean up
drop table info
drop procedure deleteValues

Another way is to use either a UDF Split function or an inline XML splitter

to extract individual ID to use in the query.

Move Up Or Move Down Or Move All the Way Up Or All The Way Down — T-SQL Solution With Merge

Come across this question from this thread which is similar as one custom sequencing issue I have an answer before.


Here is a solution I modified with one existing solution I had early:


Here is the sample code:


--DDL for table ToDoList
If Exists ( SELECT * FROM sys.tables JOIN sys.schemas
 ON sys.tables.schema_id = sys.schemas.schema_id
 WHERE = N'dbo' AND = N'toDoList' )
 Drop table ToDoList

Create table ToDoList (todoID int primary key, personId int, todoText varchar(50), todoSequence int)

insert into ToDoList values (61,1,'colour_thumbnails',1)
,(66,1,'sent_to_print', 2)
,(63,1,'page_approved', 3)
,(60,1,'line_drawing', 4)

 --Before new ordering
select * from ToDoList order by todoSequence ;

--Change parameters here!!!!!!!!!!

declare @personID int = 1,
 @todoSequence int =3,
 @MoveRequest int = 1
 --Move Request 0 = All the way down, 1 = One down, 2 = One up, 3 = All the way up

Merge ToDoList TDL
 CASE WHEN todoSequence = @todoSequence THEN
 CASE WHEN @MoveRequest=0 THEN (SELECT MAX(todoSequence) FROM ToDoList WHERE PersonID=@PersonID)+1
 WHEN @MoveRequest=1 THEN todoSequence+1
 WHEN @MoveRequest=2 THEN todoSequence-1
 WHEN @MoveRequest=3 THEN 0
 ELSE toDoSequence
 WHEN @MoveRequest<=1 AND todoSequence = @todoSequence Then todoSequence+1
 WHEN @MoveRequest>1 AND todoSequence = @todoSequence
 THEN 0 ELSE todoSequence-1
 END )
 AS RowNum

FROM ToDoList WHERE PersonID=@PersonID ) as Src (todoID, RowNum)
 On TDL.todoID=Src.todoID

WHEN Matched Then
 UPDATE SET todoSequence=Src.RowNum;

select * from ToDoList
order by todoSequence





MERGE a subset of the target table — Pitfall and Options

I have used MERGE for sometime now. It is a very useful feature added to SQL Server 2008.  I have used MERGE to come up with some solutions to my real production code.

When it is time to use a subset of the target table to manipulate, it is attempting to add the condition to the ON clause like to filter our some records for the target table. But this is not the correct way to do it.

I have run into this issue for a question posted at MSDN:

Tom Cooper pointed out the issue in another related thread:


To better understand this issue, I serached to find a detailed example from Paul White to discussing about this issue:

Here is a fully documented link from MSDN:

and the definition of MERGE documentation:


To solve the problem, we have three options: Use the filter at the WHEN clause; use an UPDATABLE View; use CTE.

I would like to use WHEN if I want a quick fix. To make it more clear, I will write the code starting with a CTE block before the MERGE keyword and use the CTE  as target table.