![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------
--用途:删除一条记录
--项目名称:
--说明:
--时间:2008/7/23 9:23:41
------------------------------------
ALTER PROCEDURE [dbo].[deleteinfo]
@UserID int,
@UserName varchar(20)
AS
begin
declare @studentid varchar(20)
declare @users varchar(max)
declare @userCD int
declare @type tinyint
declare @users_new varchar(max)
--事务
begin transaction trans
--获取ID号
select @studentid=studentid from tbl_inner_studentinfo where StuNumber = @UserName
print(@studentid)
DECLARE @_CURSOR_SQL CURSOR
SET @_CURSOR_SQL = CURSOR FOR
SELECT [UserID]
,[Users]
,[AppraiseType]
FROM table1
where ','+Users+',' like '%,' + @studentid + ',%'
or ','+Users+',' like '%,A' + @studentid + ',%'
--清除评价信息
open @_CURSOR_SQL
fetch next from @_CURSOR_SQL into @userCD, @users, @type
while @@FETCH_STATUS = 0
begin
--修值
set @users_new = REPLACE(@users,'A'+@studentid+',','')
set @users_new = REPLACE(@users_new,@studentid+',','')
--修改
update table1
set Users = @users_new
where [UserID]=@userCD and [AppraiseType] = @type
--重新循环
FETCH NEXT FROM @_CURSOR_SQL into @userCD, @users, @type
end
CLOSE @_CURSOR_SQL
DEALLOCATE @_CURSOR_SQL
if(@@error <> 0)
begin
rollback transaction trans
end
else
begin
commit transaction trans
end
end
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO