--测试--测试数据 create table tableA(id int,SchoolID int,ClassID int,name varchar(10),score int) insert tableA select 1,1,1,'a',100 union all select 2,1,1,'b',99 union all select 3,1,1,'c',34 union all select 4,1,2,'d',33 union all select 5,2,1,'e',55create table tableB(SchoolID int,name varchar(10)) insert tableB select 1,'AA中学' union all select 2,'BB中学'create table tableC(SchoolID int,ClassID int,name varchar(10),score int,content varchar(100)) go--创建处理的存储过程 create proc p_process @SchoolID int=1, @ClassID int=1 as declare @s varchar(8000) set @s='一二三四五六七八九十' insert tableC select a.SchoolID,a.ClassID,a.name,a.score ,b.name+substring(@s,a.ClassID,1)+'班' +a.name+'同学得分'+cast(a.score as varchar) from tableA a join tableB b on a.SchoolID=b.SchoolID where a.SchoolID=@SchoolID and a.ClassID=@ClassID go--调用 exec p_process 1,1--显示处理结果 select * from tableC go--删除测试 drop table tableA,tableB,tableC drop proc p_process/*--测试结果 SchoolID ClassID name score content ----------- ----------- ---------- ----------- ------------------------- 1 1 a 100 AA中学一班a同学得分100 1 1 b 99 AA中学一班b同学得分99 1 1 c 34 AA中学一班c同学得分34(所影响的行数为 3 行) --*/
create table tableA(id int,SchoolID int,ClassID int,name varchar(10),score int)
insert tableA select 1,1,1,'a',100
union all select 2,1,1,'b',99
union all select 3,1,1,'c',34
union all select 4,1,2,'d',33
union all select 5,2,1,'e',55create table tableB(SchoolID int,name varchar(10))
insert tableB select 1,'AA中学'
union all select 2,'BB中学'create table tableC(SchoolID int,ClassID int,name varchar(10),score int,content varchar(100))
go--创建处理的存储过程
create proc p_process
@SchoolID int=1,
@ClassID int=1
as
declare @s varchar(8000)
set @s='一二三四五六七八九十'
insert tableC
select a.SchoolID,a.ClassID,a.name,a.score
,b.name+substring(@s,a.ClassID,1)+'班'
+a.name+'同学得分'+cast(a.score as varchar)
from tableA a join tableB b on a.SchoolID=b.SchoolID
where a.SchoolID=@SchoolID and a.ClassID=@ClassID
go--调用
exec p_process 1,1--显示处理结果
select * from tableC
go--删除测试
drop table tableA,tableB,tableC
drop proc p_process/*--测试结果
SchoolID ClassID name score content
----------- ----------- ---------- ----------- -------------------------
1 1 a 100 AA中学一班a同学得分100
1 1 b 99 AA中学一班b同学得分99
1 1 c 34 AA中学一班c同学得分34(所影响的行数为 3 行)
--*/