April 12, 2005 at 12:34 pm
Hi all,
I have a sproc should return the value 1 but instead it's return 2. There is just one record in tblFinal_inspection. If I commented out the out side IF statement it works. Can anyone see where I error is?
Thanks Much!
CREATE PROCEDURE dbo.spFI_status_rev_test
@inspec_id int,
@apple_part_num char(12),
@material_lotnum char(20),
:
@lotnum int out
AS
declare @CT int, @days int --, @lotnum int
if @addon_status = 0
Begin
--if @insert_lotnum = null or @insert_lotnum = ""
select lotnum
from tblFinal_inspection
where apple_part_num = @apple_part_num and material_lotnum = @material_lotnum
and cure_date = @cure_date and fi_closed = 0;
--add fi_closed to criteria to return lot numbers that are open on the same part
if @@rowcount > 0 --one or more FI sheet exist
Begin
IF @@rowcount = 1
return(1);
Else
return(2);
end
else
--unable to locate FI for the work order
return(0);
End
GO
April 12, 2005 at 1:21 pm
run this in Query analyser and you'll see the problem
Select top 1 * from dbo.SysObjects
Print @@Rowcount
--1
print @@Rowcount
--0
The rowcount variable is reupdated after the print statement. The 2nd time you call it its value is 0, therefor always going in the else.
The workaround :
Declare @MyRowCount as int
set @MyRowcount = @@Rowcount
if @MyRowcount > 0 --one or more FI sheet exist
Begin
IF @MyRowcount = 1
return(1);
Else
return(2);
end
else
--unable to locate FI for the work order
return(0);
End
also this line would be much safer written like this :
if @insert_lotnum = null or @insert_lotnum = ""
if ISNULL(@insert_lotnum, '') = ''
this is because null=null returns unknown which is never true nor false.
April 12, 2005 at 1:56 pm
Hello Remi,
Thanks for the code and explanations.
I have two questions
If the @@rowcount variable gets resetted to 0 shouldn't the return value be 0?what puzzles me is that it keeps on return 2 instead of 1 when there is just one record.
Second, if I set @MyRowcount = @@Rowcount
Wouldn't @MyRowcount reset to 0 also?
Thanks so..so much!
if @MyRowcount > 0 --one or more FI sheet exist
Begin
IF @MyRowcount = 1
return(1);
Else
return(2);
else
--unable to locate FI for the work order
return(0);
End
April 12, 2005 at 2:00 pm
no because when it is first evaluated, it is greater than 0, the value is reset after the if is evaluated.
@MyRowcount is a different variable kept in another part of the ram and is in no way related to @@rowcount besides the fact that it is populated with its value.
April 12, 2005 at 2:19 pm
Thanks Remi!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply