>>>>We upgraded our production server from 2000 to 2005 this weekend. I noticed today that a certain procedure seemed to stop working as it did in 2000.
>>>>
>>>>In this procedure, a value was returned using the RETURN statement within a BEGIN...END block, but the rest of the contents of the block was execute properly. Now it seems the RETURN statement terminates the block and promptly returns the value. Does this sound right?
>>>
>>>The RETURN staement terminates stored procedure execution immediately and returns specified value, if any. AFAIK, it works the same in SQL 2000 and SQL 2005.
>>
>>Gosh, I am clueless then. It worked fine in 2000, but I had to re-arrange the statement in 2005.
>
>It sounds more like a behaviour in 2005 is actually causing the RETURN to be hit. It may not have been hit before.
>
>How ugly is the stored proc?
>
>Can you post it?

Disclaimer: I did not write this. ;-)

You can see where I commented the first return and copied it to another location.

ALTER PROCEDURE [dbo].[usp_PasswordUpdate]
(
	@SplrLoginID varchar(50),
	@UserPassword  varchar(200)
)
AS
begin
declare @count int
   
  set @count=(select count(*) from supplier where SplrLoginID=@SplrLoginID)
  --return(select count(*) from supplier where SplrLoginID=@SplrLoginID)
  if @count=1 
   begin --Updating the current password with new password
   UPDATE Supplier SET UserPassword=@UserPassword,PasswordCreateDate=getdate(),PasswordResetFlag=0 WHERE SplrLoginID=@SplrLoginID
   end
return(select count(*) from supplier where SplrLoginID=@SplrLoginID)
end