<script type="text/javascript">
</script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
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"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</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"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>