While loop in Stored Procedure doesnt work

Question

I'm working on a database(school project). I need some testing for that database(SQL Server 2008 R2).

I'm trying to test its recovery. There for i'm building a stored procedure so that it takes long enough to crash my pc.

The problem is that the while loop that i'm using doenst seems to work.

Stored procedure:

USE [OnderzoekSQL]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Test_pro_opnemen] 
-- Add the parameters for the stored procedure here
@bnummer int OUT,
@i int

AS
BEGIN

SET NOCOUNT ON;
WHILE(@i <= @@ROWCOUNT )
    -- Insert statements for procedure here
    SELECT TOP 1 @bnummer = accountnumber
    FROM dbo.bank 
    ORDER BY saldo DESC

    PRINT @bnummer

    UPDATE bank
    SET saldo = '0'
    WHERE accountnumber = @bnummer
    SET @i = @i+1

END

And the table:

CREATE TABLE [dbo].[bank](
[accountnumber] [nvarchar](50) NOT NULL,
[saldo] [real] NULL,
[owner_id] [int] NULL;

And about the difference between the nvarchar and the int of accountnumber. It doesnt really matter because i only use number in accountnumber.

The procedure work if i remove the While loop

What about @i parameter on the first loop check? If you send @i=NULL then on first check your condition isn't true. If @i>0 the same happens. — valex
– valex, Commented Jan 14, 2013 at 11:18

Roy Dictus · Accepted Answer · 2013-01-14 10:50:16Z

1

Why do you do

WHILE(@i <= @@ROWCOUNT )

?

@@ROWCOUNT returns the number of rows affected by the last statement. So your UPDATE puts that number into @@ROWCOUNT, then you increase @i. What is it that you are trying to achieve? As soon as your UPDATE updates less rows than @i+1, your WHILE loop terminates.

answered Jan 14, 2013 at 10:50

Roy Dictus

33.3k9 gold badges64 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Roy Dictus Over a year ago

How can it never stop? That would mean that the number of rows your UPDATE affects is huge... As soon as @i reaches that number, the WHILE loop should at least stop. How many rows can you have that satisfy the UPDATE condition??

Kevin Kamer Over a year ago

there are 1000 rows. But i changes the while condition multiple times. It used to be WHILE (1 > SELECT MAX(saldo) FROM bank)

Roy Dictus Over a year ago

I really don't see the logic in what you are doing, frankly. I suggest thinking long and hard about the algorithm you need, then the solution should come quickly.

Kevin Kamer Over a year ago

is it possible that the while loop only takes the first row after its call?

Ian Preston · Accepted Answer · 2013-01-14 10:49:33Z

1

In the first loop iteration @@Rowcount will be 1 - you can test this yourself in a new query window.

Assuming @i is generally >= 1 the loop will exit on its first iteration.

answered Jan 14, 2013 at 10:49

Ian Preston

39.8k9 gold badges98 silver badges94 bronze badges

Comments

djangojazz · Accepted Answer · 2013-01-14 16:49:03Z

I agree with everyone else but I would suggest you dictate your MAX clause somehow and this may fix the issue:

Instead of @@Rowcount which returns the last statements rows affected, get something you can explicitly state and HOLD. I usually use a variable. BE AWARE there will be times with complex loops you may need three variables a begin an end, and a variable for a predicate. Sometimes I have found you may be updating a complex query from a statement, like a begin date, and you need that to be kept seperate from a variable that will increase from a set.

Here is a simple example of a loop method I would use:

declare @Table Table ( personID int identity, person varchar(8));

insert into @Table values ('Brett'),('John'),('Peter');

-- say I want to affect a whole table.  I need to get it's count and HOLD it.  You could just select an expression but a variable is more clean IMHO.
declare @Max int;

-- I should set a beginning variable and statically set it, however if you are doing an update in the middle of something you can set it with 
-- a select expression as well.
declare @Current int = 1;

-- bind the variable to the count of a table I want to update.  My example is simple, it could work with a table that is very large as well though.
select @Max = count(*) from @Table

-- see data before loop
select * From @Table;

while @Current <= @Max  -- @Current is explicitly set and so is Max.  However @Current will increment in the BEGIN END BLOCK.
BEGIN
    update @Table set person = person + 'New' where personID = @Current -- update from @Current variable 

    set @Current += 1;  -- increment up one in the loop AFTER OPERATION
END

-- see data after the loop
select *
from @Table

mpromonet · Accepted Answer · 2014-08-20 17:54:11Z

0

This may be old, but in your original WHILE statement you are only executing the first line of code after the WHILE statement. You must have a BEGIN and END in the loop.

Your loop should look something like this:

WHILE(@i <= @@ROWCOUNT )

   BEGIN

    -- Insert statements for procedure here
       SELECT TOP 1 @bnummer = accountnumber
       FROM dbo.bank 
       ORDER BY saldo DESC

       PRINT @bnummer

       UPDATE bank
       SET saldo = '0'
       WHERE accountnumber = @bnummer
       SET @i = @i+1

   END

edited Aug 20, 2014 at 17:54

mpromonet

12.1k45 gold badges68 silver badges99 bronze badges

answered Aug 20, 2014 at 17:46

jlefty

13 bronze badges

Comments

Dinesh Vaitage · Accepted Answer · 2016-02-26 09:37:21Z

0

Try this for While loop....

SET NOCOUNT ON
 DECLARE @LoopCounter INT , @MaxEmployeeId INT, 
    @EmployeeName NVARCHAR(100)

SELECT @LoopCounter = min(id) , @MaxEmployeeId = max(Id) FROM #Employee

WHILE  ( @LoopCounter IS NOT NULL AND  @LoopCounter <= @MaxEmployeeId)

BEGIN
 UPDATE TOP(1) #Employee
 SET  Status = 1, @EmployeeName = Name WHERE Id = @LoopCounter  AND Status = 0

PRINT @EmployeeName

   SELECT @LoopCounter  = min(id) FROM #Employee WHERE Id >= @LoopCounter AND Status = 0
END

answered Feb 26, 2016 at 9:37

Dinesh Vaitage

3,2031 gold badge22 silver badges17 bronze badges

Collectives™ on Stack Overflow

While loop in Stored Procedure doesnt work

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related