【文件属性】：
文件名称：sql函数和事务
文件大小：2KB
文件格式：TXT
更新时间：2017-04-02 07:44:24
sql 函数和事务
自己备用的，对别人没有用
CREATE FUNCTION SplitStr (@splitString varchar(8000), @separate varchar(10))
RETURNS @returnTable table(id int, col_Value varchar(50))
AS
BEGIN
declare @thisSplitStr varchar(50)
declare @thisSepIndex int
declare @lastSepIndex int
declare @i int
set @lastSepIndex = 0
set @i = 1
if Right(@splitString ,len(@separate)) <> @separate set @splitString = @splitString + @separate
set @thisSepIndex = CharIndex(@separate,@splitString ,@lastSepIndex)
while @lastSepIndex <= @thisSepIndex
begin
set @thisSplitStr = SubString(@splitString ,@lastSepIndex,@thisSepIndex-@lastSepIndex)
set @lastSepIndex = @thisSepIndex + 1
set @thisSepIndex = CharIndex(@separate,@splitString ,@lastSepIndex)
insert into @returnTable values(@i, @thisSplitStr)
set @i = @i + 1
end
return
END
go
--drop procedure sp_add_userFunction
create procedure sp_add_userFunction
@functionList varchar(5000),
@userId varchar(50)
as
DECLARE @count INTEGER
DECLARE @index INTEGER
declare @functionId varchar(50)
set @count = (select count(*) from SplitStr(@functionList,','))
set @index = 0
begin transaction
delete from xt_user_function where user_id = @userId
if @@error <> 0
begin
rollback transaction--发生错误则回滚事务，无条件退出l
return
end
while @index<@count
begin
set @functionId = (select col_Value from SplitStr(@functionList,',') where id = @index + 1)
insert into xt_user_function(function_id, user_id) values (@functionId, @userId)
SET @index=@index+1
end
if @@error <> 0
begin
rollback transaction--发生错误则回滚事务，无条件退出l
return
end
commit transaction --两条语句都完成，提交事务
go