Problem with WHILE and sproc with transaction

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with WHILE and sproc with transaction

Author

Topic

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-09 : 20:17:29

I am having a problem with some T-SQL. I am using SQL2k Query Analyzer to try and load test a stored proc between a few machines. The problem occurs when using one or multiple machines.

The problem is this:

When I execute the below stored proc in the WHILE clause it hangs up and will never complete.
When I execute it by itself it completes and I can run it over and over till my finger is too tired to go on.
If the WHILE completes without the "IF @RNO = '' or @RNO is null" statement being true then everything is ok.

So basically if tblRNOPool is empty then running the proc in the WHILE clause will cause a never ending transaction. If tblRNOPool has records or the proc is ran outside of the WHILE clause then it will not hang up.

Any help in understanding what is wrong would be awsome.

Here is my code:

--SQL QA:
DECLARE @RNO varchar(50),
@NUMBER INT,
@TNO varchar(50)
SELECT @NUMBER = 1
WHILE @NUMBER < 1000
BEGIN
select @TNO = 'B' + cast(@number as varchar(50))
exec usp_get_new_rno @TNO, @RNO output
select @TNO, @RNO, @NUMBER
SELECT @NUMBER = @NUMBER + 1
END

-- Stored Proc:
CREATE PROCEDURE usp_get_new_rno
@TNO varchar(50),
@RNO varchar(50) OUTPUT
AS

SET NOCOUNT ON

Declare @TYPEID INT,
@Goodid INT

SET @Goodid = 0

BEGIN TRAN GETRNO
while @Goodid = 0
begin
SELECT top 1 @RNO = RNO, @TYPEID = TYPEID FROM tblRNOPool with (ROWLOCK) WHERE used != 1
IF @RNO = '' or @RNO is null
BEGIN
SET @RNO = 'NOT AVAIL'
SET @Goodid = 1
END
ELSE
BEGIN
UPDATE tblRNOPool SET used = 1, tno = @TNO WHERE RNO = @RNO
select @Goodid = count(*) from tblRNOPool where RNO = @RNO and tno = @TNO
END
end

IF @RNO != 'NOT AVAIL'
BEGIN
INSERT INTO tblissuedRNOs
SELECT @RNO, @TYPEID, GETDATE(), @TNO FROM tblRNOPool WHERE RNO = @RNO and typeid = @typeid

DELETE FROM tblRNOPool WHERE RNO = @RNO and typeid = @typeid
COMMIT TRAN GETRNO
END
ELSE
BEGIN
INSERT INTO tblERROR
SELECT @RNO, GETDATE()
COMMIT TRAN GETRNO
END

SET NOCOUNT OFF
GO

Thanks.

Daniel
SQL Server DBA

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-09 : 20:43:33

Ok so I tried something and this works but I still dont understand why the other is failing.

I created a proc named GETRNO and it runs ok from the while clause. All the new proc does is call the old proc.

Here is the change:

--SQL QA:
DECLARE @RNO varchar(50),
@NUMBER INT,
@TNO varchar(50)
SELECT @NUMBER = 1
WHILE @NUMBER < 1000
BEGIN
select @TNO = 'B' + cast(@number as varchar(50))
exec getrno @TNO
select @TNO, @NUMBER
SELECT @NUMBER = @NUMBER + 1
END

and the new proc:

CREATE PROCEDURE GETRNO
@TNO varchar(50)
AS
declare @RNO varchar(50)
exec usp_get_new_rno @TNO, @RNO output
select @RNO
GO

I also noticed that it will hang up and never finish if I run the original proc like this:

declare @RNO varchar(50)
exec usp_get_new_rno 'B1', @RNO output
select @RNO
exec usp_get_new_rno 'B2', @RNO output
select @RNO
GO

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-10 : 11:20:52

Anyone?

Daniel
SQL Server DBA

kroky
Starting Member

14 Posts

Posted - 2004-05-11 : 12:12:42

when you call the procedure for the second time the value of @RNO is 'NOT AVAIL' and since there are no rows in tblRNOPool its value doesent change in the while in the procedure so you never satisfy IF @RNO = '' or @RNO is null and your while never ends .. .and you end with open transaction when you end the executioon ....

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-12 : 12:35:50

quote:
Originally posted by kroky

when you call the procedure for the second time the value of @RNO is 'NOT AVAIL' and since there are no rows in tblRNOPool its value doesent change in the while in the procedure so you never satisfy IF @RNO = '' or @RNO is null and your while never ends .. .and you end with open transaction when you end the executioon ....

Ok so if I was to add @RNO = NULL to the QA Query then maybe it would work?

DECLARE @RNO varchar(50),
@NUMBER INT,
@TNO varchar(50)
SELECT @NUMBER = 1
WHILE @NUMBER < 1000
BEGIN
SET @RNO = NULL
select @TNO = 'B' + cast(@number as varchar(50))
exec usp_get_new_rno @TNO, @RNO output
select @TNO, @RNO, @NUMBER
SELECT @NUMBER = @NUMBER + 1
END

I'll give this a try and see what happens...

Yup that works. So in my While loop in the stress test query as shown above, the @RNO the first time is null or ''. On the second time the value is 'Not Avail'. But shouldnt the line prior to the IF @RNO = '' or @RNO is null set @RNO to either '' or null if no records exist?

SELECT top 1 @RNO = RNO, @TYPEID = TYPEID FROM tblRNOPool with (ROWLOCK) WHERE used != 1
I would think that this should make @RNO null if 0 records are returned?

Ah but it doesnt. Hmm learn something new everyday dont we? hehe. Thanks for you help.

Daniel
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources