从存储过程返回输出 [英] Return output from stored procedure

问题描述

通过裸选择，我得到了正确的 ID

With the naked select I get the proper iden

declare @iden table (pk int);

update top (1) lockDate with (UPDLOCK) 
set dt = GETDATE()
output inserted.iden into @iden   
where dt is null 

select top (1) iden.pk
from @iden iden;

当我尝试将它放入存储过程时，我总是得到 0.

When I try and put it in a stored procedure I always get a 0.

你知道我做错了什么吗?

Do you know what I am doing wrong?

DROP PROCEDURE addLockDate

CREATE PROCEDURE addLockDate
AS  
    DECLARE @iden TABLE (pk INT);

    UPDATE TOP (1) lockDate WITH (UPDLOCK) 
    SET dt = GETDATE()
    OUTPUT inserted.iden INTO @iden   
    WHERE dt IS NULL

    RETURN SELECT TOP (1) iden.pk
           FROM @iden iden
GO

推荐答案

未经测试，但这可能有效:

Untested, but this might work:

drop PROCEDURE addLockDate
CREATE PROCEDURE addLockDate
AS  
    declare @iden table (pk int);
    update top (1) lockDate with (UPDLOCK) 
       set dt = GETDATE()
    output inserted.iden into @iden   
    where dt is null 
    return (select top (1) iden.pk
    from @iden iden);
GO

这肯定行得通:

drop PROCEDURE addLockDate
CREATE PROCEDURE addLockDate
AS  
    declare @iden table (pk int);
    declare @outint int;
    update top (1) lockDate with (UPDLOCK) 
       set dt = GETDATE()
    output inserted.iden into @iden   
    where dt is null 
    set @outint = (select top (1) iden.pk
    from @iden iden);
    return @outint;
GO

这篇关于从存储过程返回输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！