Reputation: 99
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
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
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
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
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
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