SQL语句和存储过程查询语句的流程控制

来源：ddcode收集作者：出处：巧巧读书 2006-03-26 进入讨论组

关键词：
存储过程

QQRead:http://www.qqread.com/sqlserver/f892952005.html
　　写好的存储过程如下

create  PROCEDURE  Pr_GetClass
@TeacherID  int
as

Declare @a  char(50), @b  char(50), @c  char(50), @d  char(50), @e  char(50)
select  @a=DRClass1,  @b=DRClass2,  @c=DRClass3,  @d=DRClass4,  @e=DRClass5  from  Teacher  Where  TeacherID  =  @TeacherID
DROP  TABLE  classname
create  table    classname(classname  char(50))
insert  into  classname    (classname)  values  (@a)
if  (@b  is  not  null)
begin
insert  into  classname    (classname)  values  (@b)
if  (@c  is  not  null)
   begin
       insert  into  classname    (classname)  values  (@c)
       if  (@d  is  not  null)
       begin
           insert  into  classname    (classname)  values  (@d)
           if  (@e  is  not  null)
           begin
              insert  into  classname    (classname)  values  (@e)
           end
       end
   end
end
select  *  from  classname
go

2 连表查询

我有三个表
KJ表
KJID
TeacherID
..........................................................

Teacher表

TeacherID
TeacherName
CollageID
.........................................................

Collage表

CollageID
CollageName

我想写一个SQL语句，查询所有的KJ，根据KJ的TeacherID查到TeacherName,同时根据TeacherID查到Teacher，Teacher的CollageID查到Collage，最后生成的数据集里KJ的属性里除了本身的KJName以外，还想加上TeacherName，CollageName。

语句如下

   SELECT T1.KJName, T2.TeacherName, T3.CollageName
   FROM KJ T1
   LEFT JOIN Teacher T2 ON T2.TeacherID=T1.TeacherID
   LEFT JOIN Collage T3 ON T3.CollageID=T2.CollageID