时间戳

首页 > 解决方案 > 使用一次后无法访问运行时表 @updated

sql - 使用一次后无法访问运行时表 @updated

问题描述

我有一个错误，@updated.id1因为它已经在上面声明了，如果我想要output deleted.tblId等于，@coun那么如何实现它？

alter procedure dbo.sp_updateAndUndoQryForsubmitOrder   --  sp_updateAndUndoQryForsubmitOrder 1,1
(
    @id int,
    @for nvarchar(50)=null,
    @coun nvarchar(50)=''
)
as
begin
    DECLARE @Updated table( id1 int);
        if(@for is not null)
            begin
                update tbl_orderDetails set curStatus='depo' 
                output deleted.tblId
                into @Updated
                where id=@id
                --Update the GroupName_old with old values
                update tbl_orders
                set curstatus='Ready'
                where (
                select count(*)
                from tbl_orderDetails as a
                inner join @Updated as b
                on a.tblId=b.id1
                where a.curStatus != 'depo' or a.curStatus is null)=0 and tbl_orders.oid=@updated.id1
                update tbl_order_execution set dep_date=GETDATE() where tbl_orderDetId=@Updated.id1

            end
        else
            update tbl_orderDetails set curStatus='pro' 
            output deleted.tblId
            where id=@id 
            --Update the GroupName_old with old values
            update a
            set curStatus='0'
            from tbl_orders as a
            inner join @Updated as b
            on a.oid=b.id1;
            update tbl_order_execution set dep_date = NULL;
end

标签： sqlsql-serversql-server-2012

解决方案

这个查询

update tbl_order_execution set dep_date=GETDATE() where tbl_orderDetId=@Updated.id1

有语法错误。应该是这样的

update tbl_order_execution 
set dep_date=GETDATE() 
where tbl_orderDetId in (select id1 from @Updated)

Amd 进一步向下

        update tbl_orderDetails set curStatus='pro' 
        output deleted.tblId
        where id=@id

应该是

        update tbl_orderDetails set curStatus='pro' 
        output deleted.tblId into @updated
        where id=@id

;如果您在每条语句后加上一个空行，这一切都将更具可读性。

推荐阅读