How to Update more than 1 Row of the Table Through Storedprocedure

Question

0.00/5 (No votes)

See more:

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

C#

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() + "'";
}

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

SQL

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

Posted 2-Jul-11 0:46am

LebneizTech

Updated 2-Jul-11 0:59am

#realJSOP

v4

Add a Solution

Comments

#realJSOP 2-Jul-11 6:59am

I put your code snippets into proper html tags.

3 solutions

Solution 3

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

Posted 2-Jul-11 17:02pm

LebneizTech

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

JOAT-MON · Accepted Answer · 2011-07-02T01:45:00

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

SQL

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

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:

SQL

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)

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:

SQL

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

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

And at the bottom:

SQL

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

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.

#realJSOP · Accepted Answer · 2011-07-02T00:57:00

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

I would try it this way:

SQL

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

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

How to Update more than 1 Row of the Table Through Storedprocedure

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0