我创建了一个存储过程:
CREATE PROCEDURE teacherActive
@infantId int,
@name1 varchar(10)
AS Select teacher.teacherId,teacherName,teacherWork,publicPhone,selfPhone,Email,QQ,
activName,type,typeCount,teacher.memo
from teacher,teacher_active,active
where teacher.infantId = @infantId
and teacherName like '%@name1%' and
teacher.teacherId = teacher_active.teacherId
and teacher_active.activId in (Select teacher_active.activId from teacher_active)
GO
当在sql的查询分析器中单独执行其中的sql语句时可以查出数据,但是用exec teacherActive 1,张三
来执行却无数据,请问一下是哪里的错误啊?
由于初用存储过程,不太熟练,多谢大家指导!
另外,我想在程序中得到存储过程返回的结果集,该如何操作呢?在网上查了一下可还是不十分清楚。再次感谢!
CREATE PROCEDURE teacherActive
@infantId int,
@name1 varchar(10)
AS Select teacher.teacherId,teacherName,teacherWork,publicPhone,selfPhone,Email,QQ,
activName,type,typeCount,teacher.memo
from teacher,teacher_active,active
where teacher.infantId = @infantId
and teacherName like '%@name1%' and
teacher.teacherId = teacher_active.teacherId
and teacher_active.activId in (Select teacher_active.activId from teacher_active)
GO
当在sql的查询分析器中单独执行其中的sql语句时可以查出数据,但是用exec teacherActive 1,张三
来执行却无数据,请问一下是哪里的错误啊?
由于初用存储过程,不太熟练,多谢大家指导!
另外,我想在程序中得到存储过程返回的结果集,该如何操作呢?在网上查了一下可还是不十分清楚。再次感谢!
----------------------------------------存贮过程开头写上:
set nocount on要输出时,先写上:
set nocount off
然后再:
select * from 列名。至于 set nocount on 的作用,就是不要返回没有意义的结果集。
另外,我想知道的是在java程序中调用存储过程得到结果集的操作
@infantId int,
@name1 varchar(10)
AS Select teacher.teacherId,teacherName,teacherWork,publicPhone,selfPhone,Email,QQ,
activName,type,typeCount,teacher.memo
from teacher,teacher_active,active
where teacher.infantId = @infantId
and teacherName like '%'+@name1+'%' and
teacher.teacherId = teacher_active.teacherId
and teacher_active.activId in (Select teacher_active.activId from teacher_active)
GO
---执行
exec teacherActive 1,'张三'
在程序中定义游标/过程,打开它并fetch后while