CODEWITHSUNDEEP
sql-server-2008t-sqlstored-procedureswhile-loop
Kevin Kamer
Kevin Kamer

Reputation: 99

While loop in Stored Procedure doesnt work

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

Upvotes: 0

Views: 26710

Answers (5)

Dinesh Vaitage
Dinesh Vaitage

Reputation: 3193

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

Upvotes: 0

jlefty
jlefty

Reputation: 1

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

Upvotes: 0

djangojazz
djangojazz

Reputation: 13173

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

Upvotes: 0

Roy Dictus
Roy Dictus

Reputation: 33149

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.

Upvotes: 1

Ian Preston
Ian Preston

Reputation: 39606

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.

Upvotes: 1

Related Questions