创建名为teacherinfo1的存储过程。要求:存储过程teacherinfo1有3个参数。输入参数为teacherid和type,输出参数为info。满足:根据编号(teacherid)来查询teacherinfo表中的记录。如果type的值为1时,将姓名(name)传
时间: 2023-12-14 10:38:03 浏览: 45
入输出参数info;如果type的值为2时,将职称(title)传入输出参数info;如果type的值为3时,将授课课程(course)传入输出参数info。如果查询结果为空,则输出参数info为null。
以下是实现该存储过程的代码:
```
CREATE PROCEDURE teacherinfo1
@teacherid INT,
@type INT,
@info VARCHAR(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM teacherinfo WHERE teacherid = @teacherid)
BEGIN
IF @type = 1
SELECT @info = name FROM teacherinfo WHERE teacherid = @teacherid;
ELSE IF @type = 2
SELECT @info = title FROM teacherinfo WHERE teacherid = @teacherid;
ELSE IF @type = 3
SELECT @info = course FROM teacherinfo WHERE teacherid = @teacherid;
ELSE
SELECT @info = null;
END
ELSE
SELECT @info = null;
END
```
说明:
- 存储过程名为teacherinfo1;
- 有3个参数,@teacherid和@type为输入参数,@info为输出参数;
- 使用IF语句根据@type的值选择查询teacherinfo表的哪一列,并将结果存入@info中;
- 如果查询结果为空,则将@info赋为null。