例－存储过程实现循环插入数据&&字符分割

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- 用户权限添加-- userid 用户编码-- types 类型-- SourceSql 业务范围-- StrSeprate 分割符-- manageid 管理单位和级别单位CREATE proc [dbo].[p_for_svcinsert]( @userid int, @types char(2), @SourceSql varchar(8000), @StrSeprate varchar(100), @manageid char(1))AS BEGINdeclare @count int; declare @i int;declare @ch as varchar(100);declare @str as varchar(100);select @SourceSql=dbo.F_LW_GetYWFWName(@SourceSql,',') --将业务范围变成相应的中文set @SourceSql=@SourceSql+@StrSeprate --以","分割中文得到每个业务create table #svcPwrCD(svcCD varchar(50),id int identity(1,1)); --创建临时表while(@SourceSql<>'')begin set @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1) set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'') insert into #svcPwrCD exec ('select svcPwrCD from View_svcItem where svcItemNm in ('''+@ch+''')')end delete from dbo.tb_o_pwrConfig where pwrOwn=@userid --如果用户存在权限则删除重新配置 select * from #svcPwrCD select @count= count(*) from #svcPwrCD set @i=1 while(@i<=@count) begin select @str=svcCD from #svcPwrCD where id=@i insert into dbo.tb_o_pwrConfig (pwrOwn,OwnType,svcPwrCD,unittype) values (''+@userid+'',''+@types+'',''+@str+'',''+@manageid+'') set @i=@i+1 enddrop table #svcPwrCDend