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
&
您正在看的SQLserver教程是:SQL語句和存儲過程 查詢語句的流程控制。nbsp; 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
