 |
|
|
 首页 |
名片设计 CorelDRAW Illustrator AuotoCAD Painter 其他软件 Photoshop Fireworks Flash |
|
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 返回类别: 教程 上一教程: 利用INSTR()函数防止SQL注入攻击 下一教程: MYSQL服务器的启动与停止 您可以阅读与"SQL语句和存储过程 查询语句的流程控制"相关的教程: · SQL语句和存储过程 查询语句的流程控制 · 从一个MYSQL的例子来学习查询语句 · 使用SQL MAIL收发和自动处理邮件中的扩展存储过程简介 · MS SQL数据库备份和恢复存储过程 · 分页 SQLServer存储过程 |
  |
| 快精灵印艺坊 版权所有 |
首页 会员中心在线印刷在线编辑付款方式索取样品设计指南连锁门店
|
||
