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

Author : 菜菜

Team   : FreeXploiT

Date   : 2005-07-06

Site   : http://blog.csdn.net/freexploit

drop table classname
declare @TeacherID int
declare @a char(50)
declare @b char(50)
declare @c char(50)
declare @d char(50)
declare @e char(50)
set @TeacherID=1

select @a=DRClass1, @b=DRClass2, @c=DRClass3, @d=DRClass4, @e=DRClass5 from Teacher Where TeacherID = @TeacherID

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

以上这些SQL语句能不能转成一个存储过程？我自己试了下
ALTER PROCEDURE Pr_GetClass

@TeacherID int,
@a char(50),
@b char(50),
@c char(50),
@d char(50),
@e char(50)
as

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
但是这样的话，这个存储过程就有6个变量，实际上应该只提供一个变量就可以了

主要的问题就是自己没搞清楚 @a,@b,@C,@d 等是临时变量，是放在as后面重新做一些申明的，而不是放在开头整个存储过程的变量定义。

写好的存储过程如下

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