批量删除

批量删除的存储过程加事务
存储过程
 1 USE [test]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[Delt]    Script Date: 12/06/2011 21:55:15 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 -- =============================================
 9 -- Author:        <Author,,Name>
10 -- Create date: <Create Date,,>
11 -- Description:    <Description,,>
12 -- =============================================
13 CREATE PROCEDURE [dbo].[Delt] 
14     -- Add the parameters for the stored procedure here
15     @id varchar(1000),--主键字符串
16     @tbname varchar(50),--表名字
17     @tbid varchar(50)--主键名
18 AS
19 declare @starlength int,--开始位置
20         @endlength int,--结束位置
21         @tid int,--获取的id值
22         @sql nvarchar(200),
23         @err int--出错计数
24 set @err=0
25 set @starlength=1
26 BEGIN TRANSACTION
27     -- SET NOCOUNT ON added to prevent extra result sets from
28     -- interfering with SELECT statements.
29     SET NOCOUNT ON;
30     while(@starlength<LEN(@id))
31     begin
32         set @endlength=CHARINDEX(',',@id,@starlength)
33         --print @endlength
34         if(@endlength>0)
35         begin
36             set @tid=CAST(SUBSTRING(@id,@starlength,@endlength-@starlength) as int)
37             set @sql='Delete from '+@tbname+' where '+@tbid+' = '+cast(@tid as varchar(200))
38             --print @sql
39             exec sp_executesql @sql
40             set @err=@err+@@ERROR        
41         end
42         else break
43         
44         set @starlength=@endlength+1
45     end
46     set @tid=CAST(SUBSTRING(@id,@starlength,LEN(@id)-@starlength+1 )as int)
47     set @sql='Delete from '+@tbname+' where '+@tbid+' = '+cast(@tid as varchar(200))
48     --print @sql
49     exec sp_executesql @sql
50     set @err=@err+@@ERROR
51     if(@err<>0)
52     begin
53         ROLLBACK TRANSACTION--回滚
54         print '失败'
55     end
56     else
57     begin
58         COMMIT TRANSACTION--提交事务
59         print '成功'
60     end
61     -- Insert statements for procedure here
复制代码