文件名称：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