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

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
droptableclassname
declare@TeacherIDint
declare@achar(50)
declare@bchar(50)
declare@cchar(50)
declare@dchar(50)
declare@echar(50)
set@TeacherID=1

select@a=DRClass1,@b=DRClass2,@c=DRClass3,@d=DRClass4,@e=DRClass5fromTeacherWhereTeacherID=@TeacherID

createtableclassname(classnamechar(50))
insertintoclassname(classname)values(@a)
if(@bisnotnull)
begin
insertintoclassname(classname)values(@b)

if(@cisnotnull)
begin
insertintoclassname(classname)values(@c)

if(@disnotnull)
begin
insertintoclassname(classname)values(@d)
if(@eisnotnull)
begin
insertintoclassname(classname)values(@e)
end
end
end
end

select*fromclassname

以上这些SQL语句能不能转成一个存储过程?我自己试了下
ALTERPROCEDUREPr_GetClass

@TeacherIDint,
@achar(50),
@bchar(50),
@cchar(50),
@dchar(50),
@echar(50)
as

select@a=DRClass1,@b=DRClass2,@c=DRClass3,@d=DRClass4,@e=DRClass5fromTeacherWhereTeacherID=@TeacherID
DROPTABLEclassname
createtableclassname(classnamechar(50))

insertintoclassname(classname)values(@a)
if(@bisnotnull)
begin
insertintoclassname(classname)values(@b)

if(@cisnotnull)
begin
insertintoclassname(classname)values(@c)

if(@disnotnull)
begin
insertintoclassname(classname)values(@d)
if(@eisnotnull)
begin
insertintoclassname(classname)values(@e)
end
end
end
end

select*fromclassname
但是这样的话,这个存储过程就有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。1 <script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
阅读更多
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭