如何处理游标中具有输入和输出参数的存储过程

Question

我有一个stored procedure，它持有一个查询，给我一个雇员id (input)的值为varchar (output)。

我的游标保存了一个雇员id列表，我想循环存储过程并打印每个雇员id的值。

这就是我到目前为止所尝试的:

DECLARE @MyCursor as CURSOR
DECLARE @InputPar as int
DECLARE @OutputPar as varchar(30)
SET @MyCursor = CURSOR FOR <query>
Open @MyCursor;
FETCH NEXT FROM @MyCursor INTO @InputPar
BEGIN
WHILE @@FETCH_STATUS = 0
Exec dbo.mySP @InputPar, @OutputPar Output
Print @OutputPar
FETCH NEXT FROM @MyCursor INTO @InputPar
END
CLOSE @MyCursor;
DEALLOCATE @MyCursor;

我现在得到的错误是Must declare the scalar variable "@QutputPar".上面声明了变量，所以我看不出问题在哪里。谁能帮我让我的代码运行?

Answer 1

DECLARE @InputPar as int
DECLARE @OutputPar as varchar(30)
declare MyCursor cursor for <your query here>
Open MyCursor;
FETCH NEXT FROM MyCursor INTO @InputPar
Exec dbo.mySP @InputPar, @OutputPar Output
Print @OutputPar
CLOSE MyCursor;
DEALLOCATE MyCursor;

Answer 2

你通过游标做的原因是未知的，它可以替换(99%的时间)与一个基于集合的操作。你没有得到值的原因是你没有正确读取输出值。

方法如下

   Declare @var int/varchar--delcare datatype as per u r need
    Exec dbo.mySP @InputPar, @var Output
    Print @var