批量删除

批量删除的存储过程加事务

USE [test]
GO
/****** Object:  StoredProcedure [dbo].[Delt]    Script Date: 12/06/2011 21:55:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Delt] 
    -- Add the parameters for the stored procedure here
    @id varchar(1000),--主键字符串
    @tbname varchar(50),--表名字
    @tbid varchar(50)--主键名
AS
declare @starlength int,--开始位置
        @endlength int,--结束位置
        @tid int,--获取的id值
        @sql nvarchar(200),
        @err int--出错计数
set @err=0
set @starlength=1
BEGIN TRANSACTION
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    while(@starlength<LEN(@id))
    begin
        set @endlength=CHARINDEX(',',@id,@starlength)
        --print @endlength
        if(@endlength>0)
        begin
            set @tid=CAST(SUBSTRING(@id,@starlength,@endlength-@starlength) as int)
            set @sql='Delete from '+@tbname+' where '+@tbid+' = '+cast(@tid as varchar(200))
            --print @sql
            exec sp_executesql @sql
            set @err=@err+@@ERROR        
        end
        else break
        
        set @starlength=@endlength+1
    end
    set @tid=CAST(SUBSTRING(@id,@starlength,LEN(@id)-@starlength+1 )as int)
    set @sql='Delete from '+@tbname+' where '+@tbid+' = '+cast(@tid as varchar(200))
    --print @sql
    exec sp_executesql @sql
    set @err=@err+@@ERROR
    if(@err<>0)
    begin
        ROLLBACK TRANSACTION--回滚
        print '失败'
    end
    else
    begin
        COMMIT TRANSACTION--提交事务
        print '成功'
    end
    -- Insert statements for procedure here

复制代码