新手请问SQL批量删除数据

新手请教SQL批量删除数据
刚遇到的一个问题，比如我要删除主键为(2,4,9)的三条数据，一开始我用的 delete table where id in (2,4,9)

在数据库里运行OK，问题出在，我调用ADO.NET SqlParameter对象传值时出问题了，我拼接了string num="2,4,9"

然后赋值 new SqlParameter("@num", num)，问题出现了，系统执行时返回“无法将一个字符串格式转换int类型”

求大大们讲讲！

[解决办法]
以前做的时候也遇到这样的问题，我的方法是将例如“1,2,3,4,5”的字符串拆分再操作
具体如下：
函数：
将由数字加逗号组成的字符串转换成数字
create FUNCTION [dbo].[intstringtoint] (
@sText NVARCHAR(Max),
@sDelim CHAR(1)
)
  
RETURNS @retArray TABLE (
value VARCHAR(100)
)
AS
BEGIN
DECLARE
@posStart BIGINT,
@posNext BIGINT,
@valLen BIGINT,
@sValue NVARCHAR(100);
  
IF @sDelim IS NULL
BEGIN
IF LEN(@sText)>100 SET @sText = SUBSTRING(@sText, 1, 100)

INSERT @retArray (value)
VALUES (@sText);
END
ELSE
BEGIN
SET @posStart = 1;
  
WHILE @posStart <= LEN(@sText)
BEGIN
SET @posNext = CHARINDEX(@sDelim, @sText, @posStart);
  
IF @posNext <= 0
SET @valLen = LEN(@sText) - @posStart + 1;
ELSE
SET @valLen = @posNext - @posStart;
  
SET @sValue = SUBSTRING(@sText, @posStart, @valLen);
SET @posStart = @posStart + @valLen + 1;
  
IF LEN(@sValue) > 0
BEGIN
IF LEN(@sValue)>100 SET @sValue = SUBSTRING(@sValue, 1, 100)

INSERT @retArray (value)
VALUES (@sValue);
END
END
END
RETURN
END 
--测试
/*DECLARE @t VARCHAR(50)
SET @t ='1,2'
SELECT @t
SELECT id, ArtNum FROM TB_Article_Info WHERE id IN (SELECT * from dbo.intstringtoint(@t,','))
------
--程序里边直接把类似 delete from tb1 where id in (SELECT * from dbo.intstringtoint(tb2,','))的语句写进去就行了，希望对你有用
[解决办法]
直接把字符串替换一下就行了
例文本框输入 2,4,9
string a,b;
a = TextBox1.Text.Replace(",","','");//这里把,替换成',' 
//于是文本框的值就变成 2','4','9
b = "'" + a + "'";
//这里再前后加上' 就变成'2','4','9'
下面简单啦，直接上sql
[解决办法]
首先你要把2,4,9变成int的啊，
定义个数组 arr[]=num.split(',')
这样arr[0]=2,arr[1]=4,arr[2]=9.
在遍历数组，删除每一项
for(int i=0;i<3;i++){
int number=arr[i];
new SqlParameter("@num", number)}
[解决办法]

探讨

首先你要把2,4,9变成int的啊，
定义个数组 arr[]=num.split(',')
这样arr[0]=2,arr[1]=4,arr[2]=9.
在遍历数组，删除每一项
for(int i=0;i<3;i++){
int number=arr[i];
new SqlParameter("@num", number)}

[解决办法]
链接： http://www.sommarskog.se/arrays-in-sql-2005.html#CLR

一个老外总结了一共11种方法：

There are at least a dozen ways to pass an array or list to a query; each has their own unique pros and cons. 

?Table-Valued Parameters. SQL 2008 and higher only, and probably the closest to a universal "best" approach.
?The Iterative Method. Pass a delimited string and loop through it. 
?Using the CLR. SQL 2005 and higher from .NET languages only. 
?XML. Very good for inserting many rows; may be overkill for SELECTs.
?Table of Numbers. Higher performance/complexity than simple iterative method.

?Fixed-length Elements. Fixed length improves speed over the delimited string.
?Function of Numbers. Variations of Table of Numbers and Fixed-length where the number are generated in a function rather than taken from a table.
?Recursive Common Table Expression (CTE). SQL 2005 and higher, still not too complex and higher performance than Iterative method.
?Dynamic SQL. Can be slow and has security implications. 
?Passing the List as Many Parameters. Tedious and error prone, but simple. 
?Really Slow Methods. Methods that uses charindex, patindex or LIKE.

查看更多 下一篇