Delete Multiple Integers as Parameter from an Int Column


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

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
go
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.

Advertisements

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.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7d34ccbc-5839-45af-9eaf-e8756d530a0f/sql-stored-procedure-help-please-help-me-complete-this-stored-proc-having-trouble-getting-around

 

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

https://jingyangli.wordpress.com/2013/06/17/custom-sequence-numbering-2-row_number-and-merge-solution/

 

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 sys.schemas.name = N'dbo' AND sys.tables.name = N'toDoList' )
 Begin
 Drop table ToDoList
 End

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)
,(64,1,'photo_taken',5)
,(65,1,'photo_approved',6)
,(55,1,'line_drawing_photo',7)


 --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
 USING (
 SELECT todoID,
 ROW_NUMBER() OVER(ORDER BY
 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
 END
 ELSE toDoSequence
 END,
 CASE
 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 target.id=2 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:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/38e82c13-af2f-45d2-98f6-a77c686ef6f4/update-column-with-values-from-the-same-column#4d037492-f0ec-4c26-aaf3-bfcc4b52aada

Tom Cooper pointed out the issue in another related thread:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/4a2ad67c-bff5-4dba-bf71-1b8004be90ce/update-column-with-values-from-the-same-column#6ebd6bdb-7bd9-4989-bc89-62d378a6adc2

 

To better understand this issue, I serached to find a detailed example from Paul White to discussing about this issue: http://dba.stackexchange.com/questions/30633/merge-a-subset-of-the-target-table

Here is a fully documented link from MSDN:  http://technet.microsoft.com/en-us/library/bb522522%28v=sql.105%29.aspx

and the definition of MERGE documentation: http://technet.microsoft.com/en-us/library/bb510625(v=sql.105).aspx

 

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.