34,576
社区成员
发帖
与我相关
我的任务
分享
-- 建[User]表
create table [User]
(ID int identity(1,1),
UserName varchar(10))
insert into [User](UserName)
select 'Z3'
-- 建[Group]表
create table [Group]
(ID int,groupcode int,perid int)
insert into [Group]
select 1,101,3 union all
select 2,202,4 union all
select 3,101,6 union all
select 4,101,7 union all
select 5,202,8
-- 建assess表
create table assess
(userid int,perid int)
-- 建存储过程
create proc sp0709
(@x varchar(100))
as
begin
set nocount on
declare @userlist varchar(100),@groupname varchar(100)
select @userlist=substring(@x,1,charindex(';',@x,1)-1),
@groupname=substring(@x,charindex(';',@x,1)+1,100)
create table #t(ID int,UserName varchar(10))
set xact_abort on
begin tran
insert into [User](UserName)
output inserted.* into #t
select t1.UserName
from
(select substring(a.s,b.number,charindex(',',a.s+',',b.number)-b.number) 'UserName'
from (select @userlist 's') a,master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.s)
and substring(','+a.s,b.number,1)=',') t1
left join [User] t2 on t1.UserName=t2.UserName
where t2.UserName is null
insert into assess(userid,perid)
select a.ID,b.perid
from #t a
cross join
(select perid
from [Group]
where groupcode=@groupname) b
commit tran
select * from #t
end
-- 执行
exec sp0709 @x='z1,z2,z3;101'
-- 存储过程返回结果
/*
ID UserName
----------- ----------
2 z1
3 z2
*/
-- 表数据结果
select * from [User]
/*
ID UserName
----------- ----------
1 Z3
2 z1
3 z2
(3 row(s) affected)
*/
select * from assess
/*
userid perid
----------- -----------
2 3
2 6
2 7
3 3
3 6
3 7
(6 row(s) affected)
*/