如何通过存储过程更新多于1行的表 [英] How to Update more than 1 Row of the Table Through Storedprocedure

问题描述

我正在使用For LOOP更新具有存储在DataTable中的代码的Multipal行，就像...

I am using For LOOP to Update Multipal Rows with Refrence of Code stored in DataTable,Like ...

for (int i = 0; i < dtt.Rows.Count; i++)
{
    string s1 =   "update items set IQty=IQty-'" + txtQ.Text + "' where        ItemCode='" + dtt.Rows[i][0].ToString() + "'";
}

现在我想通过storedprocedure进行操作.我正在尝试下面的代码.但是它不起作用.请更正它.

Now I want to do it through storedprocedure.I am trying the code below.But it is not working.Please correct it.

select @Tot=count(*) from Pl_Material_Stock_Closing_Daily where MSLId=@MSLId and E_date>@E_date
set @I=0								
while(@I<@Tot)
Begin
select @Id=A.MSLId from Pl_Material_Stock_Closing_Daily A where A.MSLId=(select Min(MSLId) from Pl_Material_Stock_Closing_Daily where MSLId=@MSLId and MSLId>@Id)
set @I=@I+1								
End								
UPDATE Pl_Material_Stock_Closing_Daily set QtyS = QtyS+@QtyS where MSLId=@Id and E_date=@E_date

推荐答案

但是" s不能正常工作"什么也没告诉我们.

我会这样尝试:

"But it''s not working" doesn''t tell us anything.

I would try it this way:

SET @Tot = (SELECT COUNT(*) FROM Pl_Material_Stock_Closing_Daily WHERE MSLId=@MSLId AND E_date > @E_date
SET @I = 0
WHILE(@I < @Tot)
BEGIN
    SET @Id = (SELECT @Id=A.MSLId 
               FROM Pl_Material_Stock_Closing_Daily A 
               WHERE A.MSLId = (SELECT Min(MSLId) 
                                FROM Pl_Material_Stock_Closing_Daily 
                                WHERE MSLId=@MSLId 
                                AND MSLId>@Id)

    UPDATE Pl_Material_Stock_Closing_Daily 
    SET QtyS = QtyS + @QtyS 
    WHERE MSLId=@Id 
    AND E_date = @E_date 

    SET @I = @I + 1
END

我不是SQL专家，但昨晚确实住在Holiday Inn Express.

I''m not a SQL expert, but I did stay at a Holiday Inn Express last night.

不工作"可能意味着很多事情.
让我们从顶部开始:

"Not working" can mean alot of things.
Let''s start at the top:

select @Tot=count(*) from Pl_Material_Stock_Closing_Daily where MSLId=@MSLId and E_date>@E_date

您是否已验证返回的计数大于0?如果不是，那么您将永远不会进入while循环.

现在让我们看一下select语句:

Have you verified that this is returning a count that is greater than 0? If it is not, then you will never enter the while loop.

Now lets look at the select statement:

select @Id=A.MSLId from Pl_Material_Stock_Closing_Daily A where A.MSLId=(select Min(MSLId) from Pl_Material_Stock_Closing_Daily where MSLId=@MSLId and MSLId>@Id)

在这里，子查询只能返回MSLID(如果它等于变量@MSLId并且由于where子句而大于变量@Id). MIN()是无意义的(由于MSLId = @ MSLId)，但是它意味着您打算做的事情与您已做的事情不同.等效的语句是:

Here, the sub-query can only return the MSLID if it is equal to the variable @MSLId and greater than the variable @Id because of the where clause. MIN() is meaningless (because of the MSLId=@MSLId), but it implies that you are intending to do something different than what you have done. An equivalent statement is:

SELECT @Id=MSLId FROM P1_Material_Stock_Daily WHERE MSLId=@MSLId AND MSLId>@ID

您可以看到Pl_Material_Stock_Closing_Daily.MSLId，@ MSLId和@Id之间的循环引用吗?

并在底部:

Can you see the circular reference between Pl_Material_Stock_Closing_Daily.MSLId, @MSLId, and @Id?

And at the bottom:

UPDATE Pl_Material_Stock_Closing_Daily set QtyS = QtyS+@QtyS where MSLId=@Id and E_date=@E_date

此UPDATE在while循环之外，因此不会像您指示的那样在每次迭代中进行更新.

您可能需要给我们一个更好的主意，如果要修复此代码，您将要尝试完成什么，以及对不起作用"的含义有更好的理解.

This UPDATE is outside the while loop so it will not be updating on each iteration as you indicated you needed it to.

You may need to give us a better idea what you are trying to accomplish and a better understanding of what "not working" means if you want to fix this code.

亲爱的先生，我有尝试了修改后的代码，我也根据我的Requrement进行了编辑，但是它不起作用.我认为过程是正确的，它在LOOP内，但是在不满足条件的情况下，输出为:(受影响的0行)当我尝试更新>的行时.该MSLId的E_date(行可能是0/1/> 1).我正在提供完整的StoredProcedure.[是否存在像.NET(Break Point)一样逐行检查StoredProcedure中的过程]

更改过程[dbo].[sp_Ins_Up_TestIF]
(
@MSLId int，
@QtyS十进制，
@TDate varchar(50)，
@E_date datetime，
@CC varchar(50)，
@U_ID uniqueidentifier，
@Br_ID uniqueidentifier
)
如
声明@Q十进制，@ I int，@ Id datetime，@ Tot int
开始

更新Pl_Material_Stock_Level集Qty = Qty + @ QtyS，其中MSLId = @ MSLId
如果不存在(从Pl_Material_Stock_Closing_Daily中选择MSLId，其中MSLId = @ MSLId和E_date = @ E_date)
开始
从Pl_Material_Stock_Closing_Daily中选择前1个@ Q = QtyS，其中MSLId = @ MSLId和E_date< @E_date按E_Date desc排序
设置@ Q = @Q + @QtyS
插入到Pl_Material_Stock_Closing_Daily(MSLId，QtyS，TDate，E_date，CC，U_ID，BR_ID)值(@ MSLId，@ Q，@ TDate，@ E_date，@ CC，@ U_ID，@ Br_ID)
如果存在(从Pl_Material_Stock_Closing_Daily中选择MSLId，其中MSLId = @ MSLId和E_date> @E_date)
从Pl_Material_Stock_Closing_Daily中选择@ Tot = COUNT(*)，其中MSLId = @ MSLId和E_date> @E_date
设置@I = 0
WHILE(@I< @Tot)
开始
从Pl_Material_Stock_Closing_Daily的SELECT @ Id = A.E_date到A.E_date =(从Pl_Material_Stock_Closing_Daily的SELECT Min(E_date)到MSLId = @ MSLId和E_date> @Id)
从Pl_Material_Stock_Closing_Daily每天SELECT @ Id = E_date MSLId = @ MSLId AND E_date> @ID
更新Pl_Material_Stock_Closing_Daily SET QtyS = QtyS + @QtyS，其中MSLId = @ MSLId和E_date = @ Id
SET @I = @I +1
END
END
ELSE
开始
UPDATE Pl_Material_Stock_Closing_Daily设置QtyS = QtyS + @ QtyS，其中MSLId = @ MSLId和E_date = @ E_date
如果存在(从Pl_Material_Stock_Closing_Daily中选择MSLId，其中MSLId = @ MSLId和E_date> @E_date)
从Pl_Material_Stock_Closing_Daily中选择@ Tot = COUNT(*)，其中MSLId = @ MSLId和E_date> @E_date
设置@I = 0
WHILE(@I< @Tot)
开始
从Pl_Material_Stock_Closing_Daily的SELECT @ Id = A.E_date到A.E_date =(从Pl_Material_Stock_Closing_Daily的SELECT Min(E_date)到MSLId = @ MSLId和E_date> @Id)
从Pl_Material_Stock_Closing_Daily每天SELECT @ Id = E_date MSLId = @ MSLId AND E_date> @ID
更新Pl_Material_Stock_Closing_Daily SET QtyS = QtyS + @QtyS，其中MSLId = @ MSLId和E_date = @ Id
SET @I = @I +1
END
END
结束

Dear Sir, I have tryed your modified Code,I also Edit according to my Requrement,but it is not working.I think procedure is correct,it is Inside the LOOP,but where Condition is not satisfied,so output is : (0 row(s) affected).As I try to UPDATE the Rows which is > E_date of that MSLId (Rows may be 0/1/>1).I am giving complete StoredProcedure.[Is there process to check row by row in StoredProcedure like .NET(Break Point)]

Alter procedure [dbo].[sp_Ins_Up_TestIF]
(
@MSLId int,
@QtyS decimal,
@TDate varchar(50),
@E_date datetime,
@CC varchar(50),
@U_ID uniqueidentifier,
@Br_ID uniqueidentifier
)
as
declare @Q decimal, @I int,@Id datetime,@Tot int
begin

update Pl_Material_Stock_Level set Qty=Qty+@QtyS where MSLId =@MSLId
IF NOT EXISTS(select MSLId from Pl_Material_Stock_Closing_Daily where MSLId=@MSLId and E_date=@E_date)
BEGIN
select top 1 @Q=QtyS from Pl_Material_Stock_Closing_Daily where MSLId=@MSLId and E_date<@E_date order by E_Date desc
set @Q= @Q + @QtyS
insert into Pl_Material_Stock_Closing_Daily(MSLId,QtyS,TDate,E_date,CC,U_ID,BR_ID) values ( @MSLId,@Q,@TDate,@E_date,@CC,@U_ID,@Br_ID)
IF EXISTS(select MSLId from Pl_Material_Stock_Closing_Daily where MSLId=@MSLId and E_date>@E_date)
SELECT @Tot= COUNT(*) FROM Pl_Material_Stock_Closing_Daily WHERE MSLId=@MSLId AND E_date > @E_date
set @I = 0
WHILE(@I < @Tot)
BEGIN
SELECT @Id=A.E_date FROM Pl_Material_Stock_Closing_Daily A WHERE A.E_date = (SELECT Min(E_date) FROM Pl_Material_Stock_Closing_Daily WHERE MSLId=@MSLId and E_date>@Id)
SELECT @Id=E_date FROM Pl_Material_Stock_Closing_Daily WHERE MSLId=@MSLId AND E_date>@ID
UPDATE Pl_Material_Stock_Closing_Daily SET QtyS = QtyS + @QtyS WHERE MSLId=@MSLId and E_date=@Id
SET @I = @I + 1
END
END
ELSE
begin
UPDATE Pl_Material_Stock_Closing_Daily set QtyS = QtyS+@QtyS where MSLId=@MSLId and E_date=@E_date
IF EXISTS(select MSLId from Pl_Material_Stock_Closing_Daily where MSLId=@MSLId and E_date>@E_date)
SELECT @Tot= COUNT(*) FROM Pl_Material_Stock_Closing_Daily WHERE MSLId=@MSLId AND E_date > @E_date
set @I = 0
WHILE(@I < @Tot)
BEGIN
SELECT @Id=A.E_date FROM Pl_Material_Stock_Closing_Daily A WHERE A.E_date = (SELECT Min(E_date) FROM Pl_Material_Stock_Closing_Daily WHERE MSLId=@MSLId and E_date>@Id)
SELECT @Id=E_date FROM Pl_Material_Stock_Closing_Daily WHERE MSLId=@MSLId AND E_date>@ID
UPDATE Pl_Material_Stock_Closing_Daily SET QtyS = QtyS + @QtyS WHERE MSLId=@MSLId and E_date=@Id
SET @I = @I + 1
END
END
End

这篇关于如何通过存储过程更新多于1行的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！