27,580
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('pro_test')is not null
drop proc pro_test
go
create proc pro_test
(
@teachername varchar(10),
@stuname varchar(50),
@state bit
)
as
begin
--创建临时表存储你传进来的数据
if OBJECT_ID('temp..#test')is not null
drop table #test
create table #test(
teachername varchar(10),
stuname varchar(50),
[state] bit
)
--插入数据
insert #test
select @teachername,@stuname,@state
--将你传来的数据进行以下查询处理后在插入考勤表
insert 考勤表
select A.teachername, B.stuname,A.[state]
from(
select teachername,stuname=convert(xml,' <root> <v>' +
replace(stuname, ',', ' </v> <v>') + ' </v> </root>'),[state]
from #test
)A
outer apply(
select stuname = N.v.value('.', 'varchar(100)')
from A.stuname.nodes('/root/v') N(v)
)B
end
if OBJECT_ID('pro_test')is not null
drop proc pro_test
go
create proc pro_test
(
@teachername varchar(10),
@stuname varchar(50),
@state bit
)
as
begin
if OBJECT_ID('temp..#test')is not null
drop table #test
create table #test(
teachername varchar(10),
stuname varchar(50),
[state] bit
)
insert #test
select @teachername,@stuname,@state
insert 你插入的表
select A.teachername, B.stuname,A.[state]
from(
select teachername,stuname=convert(xml,' <root> <v>' +
replace(stuname, ',', ' </v> <v>') + ' </v> </root>'),[state]
from #test
)A
outer apply(
select stuname = N.v.value('.', 'varchar(100)')
from A.stuname.nodes('/root/v') N(v)
)B
end
--都给你说那么明确了啊