批量删除
编辑器加载中.
自己写的，利用事务和存储过程，进行批量删除
View Code
1 -- ================================================
2 -- Template generated from Template Explorer using:
3 -- Create Procedure (New Menu).SQL
4 --
5 -- Use the Specify Values for Template Parameters
6 -- command (Ctrl-Shift-M) to fill in the parameter
7 -- values below.
8 --
9 -- This block of comments will not be included in
10 -- the definition of the procedure.
11 -- ================================================
12 USE [test]
13 GO
14 /****** Object: StoredProcedure [dbo].[Delt] Script Date: 12/06/2011 21:55:15 ******/
15 SET ANSI_NULLS ON
16 GO
17 SET QUOTED_IDENTIFIER ON
18 GO
19 -- =============================================
20 -- Author: <Author,,Name>
21 -- Create date: <Create Date,,>
22 -- Description: <Description,,>
23 -- =============================================
24 CREATE PROCEDURE [dbo].[Delt]
25 -- Add the parameters for the stored procedure here
26 @id varchar(1000),--主键字符串
27 @tbname varchar(50),--表名字
28 @tbid varchar(50)--主键名
29 AS
30 declare @starlength int,--开始位置
31 @endlength int,--结束位置
32 @tid int,--获取的id值
33 @sql nvarchar(200),
34 @err int--出错计数
35 set @err=0
36 set @starlength=1
37 BEGIN TRANSACTION
38 -- SET NOCOUNT ON added to prevent extra result sets from
39 -- interfering with SELECT statements.
40 SET NOCOUNT ON;
41 while(@starlength<LEN(@id))
42 begin
43 set @endlength=CHARINDEX(',',@id,@starlength)
44 --print @endlength
45 if(@endlength>0)
46 begin
47 set @tid=CAST(SUBSTRING(@id,@starlength,@endlength-@starlength) as int)
48 set @sql='Delete from '+@tbname+' where '+@tbid+' = '+cast(@tid as varchar(200))
49 --print @sql
50 exec sp_executesql @sql
51 set @err=@err+@@ERROR
52 end
53 else break
54
55 set @starlength=@endlength+1
56 end
57 set @tid=CAST(SUBSTRING(@id,@starlength,LEN(@id)-@starlength+1 )as int)
58 set @sql='Delete from '+@tbname+' where '+@tbid+' = '+cast(@tid as varchar(200))
59 --print @sql
60 exec sp_executesql @sql
61 set @err=@err+@@ERROR
62 if(@err<>0)
63 begin
64 ROLLBACK TRANSACTION--回滚
65 print '失败'
66 end
67 else
68 begin
69 COMMIT TRANSACTION--提交事务
70 print '成功'
71 end
72 -- Insert statements for procedure here
..
