创建名为teacherinfo1的存储过程。要求:存储过程teacherinfo1有3个参数。输入参数为teacherid和type,输出参数为info。满足:根据编号(teacherid)来查询teacherinfo表中的记录。如果type的值为1时,将姓名(name)传
时间: 2023-12-10 11:39:19 浏览: 57
入到输出参数info中;如果type的值为2时,将年龄(age)传入到输出参数info中;如果type的值为3时,将性别(gender)传入到输出参数info中。如果teacherid不存在,则将输出参数info的值设为NULL。
以下是存储过程teacherinfo1的代码:
```
CREATE PROCEDURE teacherinfo1
@teacherid int,
@type int,
@info varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM teacherinfo WHERE teacherid = @teacherid)
BEGIN
SET @info = NULL;
RETURN;
END
IF @type = 1
BEGIN
SELECT @info = name FROM teacherinfo WHERE teacherid = @teacherid;
END
ELSE IF @type = 2
BEGIN
SELECT @info = CAST(age AS varchar(10)) FROM teacherinfo WHERE teacherid = @teacherid;
END
ELSE IF @type = 3
BEGIN
SELECT @info = gender FROM teacherinfo WHERE teacherid = @teacherid;
END
END
```
存储过程中首先判断输入的teacherid是否存在于teacherinfo表中,如果不存在则将输出参数info设为NULL。然后根据输入的type值来选择要传入到输出参数info中的字段。注意:在查询年龄时需要使用CAST函数将其转换为varchar类型。