SQL语句转存储过程
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个变量，实际上应该只提供一个变量就可以了
有什么办法吗？谢谢了
问题点数：20、回复次数：8Top
1 楼singlepine（小山）回复于 2005-05-25 07:56:42 得分 0
是需要六个参数的,要不然你的@TeacherID,@a,@b,@c,@d,@e这些值将怎么知道呢
create 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
goTop
2 楼colinliu（流浪人）回复于 2005-05-25 08:05:01 得分 0
同意楼上Top
3 楼joephoenix（迎风的狼）回复于 2005-05-25 08:42:33 得分 0
变量没什么问题吧！Top
4 楼renyu732（Sysinfo）回复于 2005-05-25 08:43:18 得分 0
StudyTop
5 楼xzq686（★_瞬_★）回复于 2005-05-25 08:57:05 得分 20
存储过程的参数只要一个就行。
那几个是在存储过程中定义的局部参数。因为调用存储过程的时候只有一个输入参数@TeacherID
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
Top
6 楼yishan116（哈哈）回复于 2005-05-25 08:57:52 得分 0
singlepine(小山)
我看没问题，支持Top
7 楼cabxyz（cab）回复于 2005-05-25 09:09:10 得分 0
去数据库区Top
8 楼liuqm（blue）回复于 2005-05-25 09:27:39 得分 0
试试就知道，Top
